MySQL CONVERT_TZ()

by

If this yields null then the TZ tables have not been set up:

SELECT CONVERT_TZ(now(),'US/Eastern','US/Central');

If you do not have the time zone tables set up you could update the hour
offset in the user table and then do:

select utc_timezone() - interval user_timezone_offset_in_hours hour
from userinfo a
where user_id = 999;

You’d still need a way to update the user’s time zone however.

If you are writing this for a web application you can get the time zone via javascript, here’s an article that describes how (haven’t tried this but it looks like it’ll work).

A bit of an explanation with respect to ‘interval’ above…

One of the more trick constructs in MySQL is the use of the INTERVAL
keyword, best shown by example the (numeric value can be an expression or the field value)

select now() today, now() - interval 1 day yesterday;
+---------------------+---------------------+
| today               | yesterday           |
+---------------------+---------------------+
| 2011-05-26 13:20:55 | 2011-05-25 13:20:55 |
+---------------------+---------------------+

You can add them and subtract them anyway you like, this is why I never
bother with the date/time add/subtract/convert functions

select now() a, now() - interval 1 day + interval 4 hour + interval 8 minute b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-05-26 13:24:16 | 2011-05-25 17:32:16 |
+---------------------+---------------------+

You can use negative numbers (should be good for negative time zone offsets)
these are the same:

select now() - interval 1 month a, now() + interval -1 month b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-04-26 13:38:05 | 2011-04-26 13:38:05 |
+---------------------+---------------------+

More Related Contents:

  1. Can MySQL convert a stored UTC time to local timezone?
  2. How do I set the time zone of MySQL?
  3. How do I get the current time zone of MySQL?
  4. convert_tz returns null
  5. How to store a datetime in MySQL with timezone info
  6. MySQL timezone change?
  7. How to convert time to the time zone of the iPhone device?
  8. How to Convert UTC Date To Local time Zone in MySql Select Query
  9. MySQL Time Zones
  10. NodeJS responded MySQL timezone is different when I fetch directly from MySQL
  11. Database returned an invalid value in QuerySet.dates()
  12. MySQL: is a SELECT statement case sensitive?
  13. Check overlap of date ranges in MySQL
  14. 1114 (HY000): The table is full
  15. UPDATE multiple tables in MySQL using LEFT JOIN
  16. How to raise an error within a MySQL function
  17. What is maximum query size for mysql?
  18. Finding all parents in mysql table with single query (Recursive Query)
  19. MySQL join query using like?
  20. MySql Query: Select top 3 rows from table for each category
  21. Split a string and loop through values in MySQL stored procedure
  22. Create an index on a huge MySQL production table without table locking
  23. MySQL concurrency, how does it work and do I need to handle it in my application
  24. mysql select id and name from other table and join query
  25. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  26. Using SUM on FLOAT data
  27. Duplicate entry for key ‘group_key’
  28. SQL query with avg and group by
  29. How can I rebuild indexes and update stats in MySQL innoDB?
  30. mysql – move rows from one table to another

Leave a Comment