timezone ids and mysql such as dealing with Indiana timezones

blackhorse

php, java, objective c can use the timezone id list like this

http://php.net/manual/en/timezones.php

In mysql I only see the timezone converter like

SELECT CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00');

using the offset of the timezone.

Which cannot be accurate as timezone id when we deal with the the daylight saving changes.

My question is if I have to switch for example between the timezones of

America/Indiana/Indianapolis America/Indiana/Knox America/Indiana/Marengo America/Indiana/Petersburg

How could I do it in mysql?

Or I have to retrieve the data out, use other languages like php or java, covert among these timezones and save the data back?

Thanks!

traq

In almost any database application I've ever made, I save a date/time as a unix timestamp. I let the application (be it php, java, etc.) handle conversion to whatever timezone is appropriate for the task at hand.

Obviously, you could also have mysql do the conversion, but you would still need the parent application define what timezone offset to use.

Incidentally, you could limit yourself to 'America/Indiana/Indianapolis' (-4) and 'America/Indiana/Knox' (-5). The others are all duplicates/alternates.

blackhorse

thanks!

bradgrafelman

traq;10989419 wrote:
In almost any database application I've ever made, I save a date/time as a unix timestamp.

...and I'll advocate the exact opposite. How often is an integer like 1318829828 more useful in any way (in terms of a datetime value) as compared to '2011-10-17 12:37:08' ? (In my experience, the answer is: practically never.)

EDIT: Also note that MySQL does indeed have Time Zone Support (although it may need to be setup properly first). In other words, if you use a TIMESTAMP column, you could set the timezone of your MySQL connection to match whatever the user's timezone is and let MySQL automagically convert the TIMESTAMP from UTC (as it is stored) into the "America/Chicago" timezone (for example).

traq

bradgrafelman;10989421 wrote:
...and I'll advocate the exact opposite. How often is an integer like 1318829828 more useful in any way (in terms of a datetime value) as compared to '2011-10-17 12:37:08' ? (In my experience, the answer is: practically never.)

I often use a timestamp to compare date/times, calculate past or future dates (intervals), and I frequently need the date/time formatted in various ways depending on context and grammar. I (personally, at least) find it more convenient to do the math first, then use date() for the formatting.

I know functions exist to manipulate date/times that are in human-readable formats, but I've always preferred working with timestamps.

bradgrafelman;10989421 wrote:
EDIT: Also note that MySQL does indeed have Time Zone Support (although it may need to be setup properly first). In other words, if you use a TIMESTAMP column, you could set the timezone of your MySQL connection to match whatever the user's timezone is and let MySQL automagically convert the TIMESTAMP from UTC (as it is stored) into the "America/Chicago" timezone (for example).

I figured something like that was possible. cool : )

bradgrafelman

traq;10989434 wrote:
I often use a timestamp to compare date/times

Quick! Tell me which two dates and times I'm comparing:

SELECT *
FROM foo
WHERE time_col BETWEEN 1311819633 AND 1320018960

Now try again with this query:

SELECT *
FROM foo
WHERE time_col BETWEEN '2011-07-27 09:20:33' AND '2011-10-30 06:56:00'

traq;10989434 wrote:
calculate past or future dates (intervals)

Same drill as above... can you tell me how far into the future this query looks:

SELECT *
FROM foo
WHERE time_col < UNIX_TIMESTAMP() + 2160000

compared to this one:

SELECT *
FROM foo
WHERE time_col < NOW() + INTERVAL 25 DAY

traq;10989434 wrote:
I know functions exist to manipulate date/times that are in human-readable formats, but I've always preferred working with timestamps.

Do you also like to run your code through those nifty base64 code obfuscators before you edit them? :p

traq

bradgrafelman;10989445 wrote:
Do you also like to run your code through those nifty base64 code obfuscators before you edit them? :p

lol, no.

Weedpacket

I've so far had no cause to avoid the DBMS's native date and time types. Quite apart from the fact that using them meant I could also use the DBMS's own methods for manipulating them (including [font=monospate]SELECT some_time AT TIMEZONE 'CDT'[/font]), on a couple of occasions I've written applications that had to store dates prior to 1900 - a place where Unix timestamps just don't go. Since I've pretty much always used PostgreSQL when I had the choice, it meant that internally the date was being stored as a Julian day. (PostgreSQL also uses Julian days to store times, as fractions of a day.)

For times and which timezone to use, I always use UTC precisely because the rest change from location to location and offsets change from time to time according to no computable scheme; corrections for a particular clock gets done at the persistence layer (since the choice of timezone is usually also db-supplied). (Fortunately, so far I haven't yet had to account for leap seconds. If I had to do that I'd have a cron job checking Bulletin C....)