Link to home
Start Free TrialLog in
Avatar of courtenayt
courtenayt

asked on

java interface to internet time server?

Hi,

I don't know if this is possible, but I'm hoping someone has heard of something like this.  I'm creating a java/jsp/postgres web app and I need to insert a timestamp with timezone into the database when I do an insert.  I currently have this working by using pl/pgsql to create a trigger function that inserts 'now' into the date_submitted field in my database table.  This is all great, except I am running my application on a hosted server and they never update the server for daylight savings time, etc, so I end up with errors in my data when it is inserted (it basically ends up inserting an incorrect timezone because it is currently an hour off).

 Instead of relying on my hosted server time being accurately updated all of the time, I'm hoping that someone knows of a java interface to an internet time server that I can use to obtain the a current timestamp.  For example, a method like: getCurrentTime(tz); that qurried the internet for the current time and returned a Java TimeStamp object for the timezone passed in.

Does anyone know if something like this exists?  All I can seem to find is information relating to synchronizing a system clock.

Thanks in advance,
Courtenay
Avatar of Mayank S
Mayank S
Flag of India image

Maybe you can get the system time and then apply the time zone at the Java end. You need to use this:

http://java.sun.com/developer/technicalArticles/Intl/USDST/
Avatar of courtenayt
courtenayt

ASKER

I could do that, but I would still have to check it manually at least twice a year to make sure my calculations were right on my end.  I'm hoping I can just not worry about it by always inserting the correct time.  Plus, I never know if my hosting company is going to change the time on me.  Maybe, maybe not, maybe at the old  daylight savings time, maybe at some random time when they realize they are off.

For example, right now if I insert to the database (using the 'now' trigger) at 10am GMT-6 it records it as 11am GMT-4.  Instead, it should say 11am GMT-5.  

If I manually insert a timestamp at 10am GMT -6 it correctly records it as 11am GMT-5.  This indicates to me that the server's clock is off, but I have no idea if or when they might change it.  I figured if I could use the current timestamp from an internet time server it would always be correct in my database and I could then use the Java code to display the timestamp info to my users in their own timezone.
be safer to store timestamp as UTC and apply timezone calculation on UTC times as required
objects is correct, if you do pull the time from an internet time server, you'll be receiving it in UTC anyway, and have to apply timezone corrections in your program.

Remember also that you will have to apply network time lag corrections anyway to get a truly accurate time.

The wiki is here http://en.wikipedia.org/wiki/Network_Time_Protocol for NTP which is the standard method.

Hope this helps

Stuart


 
UTC sounds like a good idea.  Does anyone know of how to actually implement querying the current time from Java to a web server?  All documentation talks about synchronizing system clocks.  I just want to query the timeservers for current time whenever I need it.  

Luckily network lag isn't that important to me.  Even if my times are off by a few minutes, that is fine.  I was just hoping to find something more accurate than my hosting company remembering to keep their server clocks updated.

Is the clock correct except for DST shift, or do they show other time issues, like the wrong date, or major difference in minutes value as well as hour, like showing 11:49 when it's 10:15 your local time?

As the others have said, if you query a stratum-2 timesource, you will get UTC, which is "technospeak" for Greenwich Mean Time, which means you'd have to use the time zone/DST offset calculation as recommended by mayankeagle anyway.

However, since your problem appears to be when you programmatically insert a record into the database, I think you'd want to figure out why a manual insert gives you the correct timestamp.  If their server's time is incorrect, then I'd think they'd both result in the wrong time stamp.

Maybe they just need to restart their server...  it seems to me you're trying to fix a minor glitch the hard way... ;)
It's also possible that the server was patched but Java wasn't, if your app still uses the old EDT, MDT or HDT strings:
Time Zone Updater Tool for v1.4.x - http://java.sun.com/javase/tzupdater_README.html

It seems to be a problem somewhere on the server side.   I am in CST the server is EST.  If I insert a record into my postgres db using a pl/pgsql trigger that inserts 'now' into the table with the insert at 9am GMT-6 it inserts it as 10am GMT -4.  It should be inserting it as either 10am GMT-5 or 11am GMT-4.  This problem only started at the daylight savings shift around March 10th of this year.  The weird thing is if, I get the current date/time on the linux server using "date", it displays the correct time (in EST).  It seems to be something else that is wrong.  I know it is not a problem with the java code displaying it incorrectly, because I am looking at the dates in my actual database via phpPgAdmin and via psql.

If I manually insert a date (not using the trigger) it inserts it correctly.  For example if I insert a timestamp as 9am GMT-6 it shows it as 10am GMT-5 in the database table.  

My host company says they don't know why this might be.  I just figured it made more sense to not depend on the server and always get the right time no matter what from the internet.

If we can figure out why the 'now' function suddenly stopped inserting the correct timezone, that would be great!  From what everyone has says it seems like there isn't a way to just query the time from a java program.  Is that correct?
I'm not aware of a way to query an NTP server from Java, but that shouldn't be necessary.

Now() is an SQL function, AFAIK, not strictly PL/pgSQL.  The way it works is, first it checks for the timezone parameter in postgresql.conf.  If it's not set there, it checks for the TZ environment variable and uses that as its default time zone.  If it's not defined or isn't a zone known to PostgreSQL, it checks how the server OS C library function localtime() responds and makes a "best guess" based on PostgreSQL's known time zones.

It's not your hosting provider, if your manual insert is grabbing the correct time from their server but not with your trigger - it's something in the PostgreSQL config in combination with the use of the SQL now() function.  Or are you using "SELECT TIMESTAMP 'now'" instead of now()?

All timezone-aware dates and times are supposed to be stored internally in PostgreSQL using UTC and should be converted to local time in the zone specified in the timezone parameter.  Are you storing your timestamps as text conversions of the actual UTC timestamp rather than using the UTC timestamp and adjusting for local timezone at query time?
I'm not really sure what is happening.

Here is everything i know.  

1. before daylight saving this year (in march) everything was working fine.  If I used the following trigger in my postgres db to insert a timestamp it worked perfectly (a 9am GMT-6 insert was recorded as a 10am GMT-5 timestamp in the date_submitted field for that record).

   BEGIN
        -- set date_created as current date/time
        NEW.date_submitted := 'now';
        RETURN NEW;
    END;

My understanding was it was recorded as GMT-5 because that is where the host company was located and therefore how they set their servers.

2. After daylight savings in march, my trigger now inserts 9am GMT-6 as 10am GMT-4.  

Unfortunately I my host company is in charge of the postgres installation.  It sounds like you are saying I should ask them to check the postgres config file to see if it is set correctly.  I'm I understanding correctly?  I'm sure they won't change the server and postgres to UTC, as there are other people using it on my shared server and my guess is they have all of their servers, etc. set to EST.  In fact it may be set to UTC and converting when I query, but I have no idea how I would check this.  Is that something my host company should check?

Thanks,
Courtenay
What type of column is date_submitted?  Is it one of the timestamp data types or is it a text or varchar or what?

If it's a timestamp data type and you haven't recast it to not use the defaults for the data type, it should have logged the timestamp in UTC, whether it be calculated based on the settings within the database or by best-guess against the local time presented by the c library call...

As such, you should be able to issue a query that translates it to whatever time zone you want it to translate it to.

If your trigger isn't grabbing the exact same timestamp as your manual insert, something's funky.  What happens if you make a new trigger (for testing purposes) and instead of 'now' you specify a hardcoded timestamp:

NEW.date_submitted := '2007-03-31 00:00:00+00'

which should be March 31, 2007 at midnight GMT.  Your query should then return March 30, 2007 6 PM GMT-6 or 7PM GMT-5 or 8PM GMT-4.  
Hi,

My date_submitted field is type "timestamp with timezone"

I changed the trigger as you suggested to NEW.date_submitted := '2007-03-31 00:00:00+00'

I inserted a new record using the changed trigger and the result was: 2007-03-30 20:00:00-04.  This seems to be the correct time, except it is one day behind -very strange.

I get the same result from this trigger if I do the database insert via my java program or via my phpPgAdmin interface.

I checked the trigger using the 'now' function and that inserts the day correctly it inserted 2007-03-28 for today and that is correct.  The 'now' trigger inserted 2007-03-28 10:24:42.256216-04 and I inserted the record at 2007-03-28 9:28:42.256216-06.

Before daylight savings time, all timestamps created by my old trigger (using 'now') were in GMT-5.  

On my bash interface to the server the database is located on, I typed in "date" just now and pushed "enter" at 3/28/07 9:30am GMT-6 (my timezone) and the server displayed the following:  Wed Mar 28 10:27:35 EDT 2007.  This seems to be correct except for a few minutes off.

I'm not sure if that is any help, I don't know what I should do.

Thanks,
Courtenay
ASKER CERTIFIED SOLUTION
Avatar of ShineOn
ShineOn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I guess you could be right.  The time is correct.  I wasn't accounting for the fact that GMT difference would change relative to daylight savings since there is also daylight savings in the UK.  Perhaps it is because we moved up our daylight saving, or maybe I just never noticed.  So right now where I am in central time it is GMT-5.  That explains a lot, and before daylight savings it was GMT-6, that accounts for the change in the database.

Thanks!
Courtenay
One point to correct something - it is your local time that will move relative to GMT. UK daylight savings (British Summer Time - BST)  does not affect GMT - in fact BST is 1 hour ahead of GMT.

In *nix the system clock is normally set to UTC (GMT+/- a few ms).

Stuart