Wednesday, August 05, 2009

Gotcha Of The Day: Timezone Syncing

I've spent some serious debugging time trying to figure out why a Java test works fine on one machine, but fails on another. After much investigation, I realized that the timestamps being created by Java were an hour off from those created by MySQL. Naturally, this hour was caused by a timezone issue.

What I should have done first and foremost, is confirm that times are sync for all parties involved. So here you go, some quick recipes for confirming Linux, Java and MySQL are all in sync.

Linux System Time

The following will print both local, GMT time, and your current timezone offset:

date      # Wed Aug  5 09:48:01 EDT 2009
date -u   # Wed Aug  5 13:48:21 UTC 2009
date +%z  # -0400

MySQL Time

The following query will print out the local time, GMT time and the timezone offset:

mysql> select now(), utc_timestamp(), hour(now() - utc_timestamp());
+---------------------+---------------------+-------------------------------+
| now()               | utc_timestamp()     | hour(now() - utc_timestamp()) |
+---------------------+---------------------+-------------------------------+
| 2009-08-05 09:49:34 | 2009-08-05 13:49:34 |                             4 | 
+---------------------+---------------------+-------------------------------+
1 row in set (0.00 sec)

Java Time

Grab BeanShell and run the following Linux/bsh commands:

[linux]$ java -cp  bsh-2.0b4.jar bsh.Interpreter
BeanShell 2.0b4 - by Pat Niemeyer (pat@pat.net)
bsh % print(new Date());
Wed Aug 05 08:51:41 GMT-05:00 2009

The Fix

As you can see above, Linux and MySQL are in GMT-4, which according to time.gov is correct (usually, we're in GMT-5, but with DST and all,it's -4). You can also see that Java thinks we're in GMT-05:00.

The temporary fix was to run with code with -Duser.timezone=GMT-4:00, though I believe the real fix is to run the SUN's TZupdater.

2 comments:

  1. other solution is to do everything in UTC... then you can use other tools to display the time in whatever local time you are in. That solution has worked for us, and when the US Gov't decided to change daylight savings... nothing on our back end was ever affected.

    ReplyDelete
  2. Nick -

    You're absolutely right. Anything other than UTC is evil.

    Of course, when you display UTC dates, folks are always confused. But, I suppose that's more of a formatting issue, than data issue.

    ReplyDelete