Solved

java interface to internet time server?

Posted on 2007-03-22
15
569 Views
Last Modified: 2010-04-20
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
0
Comment
Question by:courtenayt
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 30

Expert Comment

by:mayankeagle
ID: 18774526
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/
0
 
LVL 1

Author Comment

by:courtenayt
ID: 18774750
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.
0
 
LVL 92

Expert Comment

by:objects
ID: 18775416
be safer to store timestamp as UTC and apply timezone calculation on UTC times as required
0
 
LVL 3

Expert Comment

by:stuartindigo
ID: 18778060
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


 
0
 
LVL 1

Author Comment

by:courtenayt
ID: 18779180
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.

0
 
LVL 35

Expert Comment

by:ShineOn
ID: 18787210
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... ;)
0
 
LVL 35

Expert Comment

by:ShineOn
ID: 18788403
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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:courtenayt
ID: 18792465
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?
0
 
LVL 35

Expert Comment

by:ShineOn
ID: 18795687
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?
0
 
LVL 1

Author Comment

by:courtenayt
ID: 18799362
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
0
 
LVL 35

Expert Comment

by:ShineOn
ID: 18804150
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.  
0
 
LVL 1

Author Comment

by:courtenayt
ID: 18808326
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
0
 
LVL 35

Accepted Solution

by:
ShineOn earned 500 total points
ID: 18811845
It's not one day behind, it's just four hours earlier, 8 PM offset UTC-4 (as I predicted) - so the timestamp is recording properly in the record.

If the 'now' trigger is inserting fairly accurately, timezone UTC-4, and the bash "date" command returns roughly the same time, UTC-4 (which is what EDT's offset is) then I think things are correct server-wise.

In fact, your statement that before DST all timestamps created by 'now' logged as GMT-5 (aka UTC-5), which is EST's offset.  The issue is that for some reason you say you're only an hour different but in GMT-6, so it should be 2 hours different.

You say you're in Central Standard Time - does that mean you're in one of those "we have to be different" cities or states where DST is ignored?  I'm in the Central time zone, which in CDT is GMT-5.  It's only GMT-6 during standard time.

I wonder if it's your local time that's wrong...
0
 
LVL 1

Author Comment

by:courtenayt
ID: 18815462
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
0
 
LVL 3

Expert Comment

by:stuartindigo
ID: 18817332
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
The viewer will learn how to implement Singleton Design Pattern in Java.
This video teaches viewers about errors in exception handling.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now