Zulu Time in REAL Server (SQLite)

UTC time is the new GMT time, the time of day unadjusted for any time zone. Zulu time is a nickname for UTC time, as "Z" appears at the end to indicate a lack of time zone adjustment.

UTC does officially account for a leap second, when an extra second is squeezed into the year every once in a while. For most business purposes we can ignore this issue.

When using world clock software such as the US federal government's to look up the current time as Zulu time, you may find "UTC" or "GMT" or "Zulu" among the list of world cities. If no such pseudo-city is listed, use Reykjavík, as Iceland is in the same time zone as UTC and avoids Daylight Saving Time. So, Reykjavík time = Zulu time.

If you have users and computers in different time zones around the country or world, and they need to share data and coordinate with one another, you may want to store your datetime values in Zulu time. 

When a New York user reads "4:15 PM" they think the event happened during their business day, but if that data was written by a Los Angeles computer, the event actually happened 3 hours later, meaning 7:15 PM, or after business hours from a New York perspective. Adding further ambiguity is the lunacy of Daylight Saving Time. If you fail to consistently record the relevant time zone with each datetime, you have a real mess.

Human brains tend to confuse such time zone adjustments. It may be wiser to adjust all local times to Zulu time before recording them. In database and other software systems, you can display the recorded Zulu time back into the user's own local time zone.

In REAL Server and presumably any other SQLite engines, you can obtain the zulu time according to the database server's computer by executing this SQL:

select datetime('now');

For example, here in Seattle the current time is 16:05 (4:05 PM). That line of code above reports:
2010-07-25 23:05:14
which is Zulu time. (Normally the west coast is 8 hours behind Zulu, but Daylight Saving Time makes that 7 hours.)

Slap a "Z" on the end, and you have a standard ISO 8601 datetime:
2010-07-25 23:05:14Z
Optionally, you can replace the middle space character with a "T": 

If you want just the date or time portion, as you would intuit, use this SQL:

select date('now');
select time('now');

If you really want the database server's local time, add a second argument 'localtime':

select datetime('now', 'localtime');
select date('now', 'localtime');
select time('now', 'localtime');

Find more info in the SQLite documentation.

No comments:

Post a Comment