Date/time conversion problem

[Recordsetclown]

I would like to display the latest UTC date/time value in my db as the date/time it would have been in my time zone. The following returns null. Can anyone tell me what is wrong with the query. Perhaps it is that I'm not referencing the time zones correctly, but I couldn't find how to determine what time zones my server will recognize.

SELECT DATE_FORMAT(CONVERT_TZ(listingsdb_last_modified,'GMT/UTC','US/Eastern'), '%M %e, %Y, %h:%i %p') AS lmd FROM default_en_listingsdb ORDER BY listingsdb_last_modified DESC LIMIT 1

Thanks for any help.

 

[r937]

check the manual, starting with the CONVERT_TZ function, which says "Time zones may be specified as described in section [blue]MySQL Server Time Zone Support[/blue]. This function returns NULL if the arguments are invalid."

the second page says "The MySQL installation procedure creates the time zone tables in the mysql database, but does not load them. You must do so manually. (If you are upgrading to MySQL 4.1.3 or later from an earlier version, you should create the tables by upgrading your mysql database. Use the instructions in section [blue]Upgrading the Grant Tables[/blue].)"



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon