mysql return time with timezone shift


Is there a way to return the time depending on the time zone? I understand that there is timestampz, but in this case datetime

those. I want to get the time in Moscow (3) based on the current saved (5) where the time in Moscow is 14:00

select dt_create + interval 2 hour from t where dt_create - interval 2 hour = 14:00

but the catch is that I don't know which fields are the time. Can I add an interval depending on the field type?


  • Time zones are related to the presentation layer. Most of your code shouldn't be concerned with time zones or local time, it should be reporting Unix time as it is.
  • When measuring time, measure Unix time. This is UTC. It's easy to get (by system functions). It has no time zones or daylight saving time (and leap seconds).
  • When storing time, store Unix time. This is one number.
  • If you want to store human-readable time (for example, in logs), try to store it along with Unix time, not instead.
  • When displaying the time, always include the timezone offset. The time format without offset is useless.
  • The system clock is not accurate.
  • You are online? Every other machine's system clock is not accurate in different ways.
  • The system clock can, and will, jump back and forth in time due to things that are out of your control. Your program must be designed to survive this.
  • The ratio of the number of seconds of the system clock to the number of real seconds is not exact and may change. It mainly depends on the temperature.
  • MySQL stores DATETIME columns as "YYYYMMDD HHMMSS" values ​​packed into numbers. If you care about storing timestamps, store them as an integer and use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions to convert.

What every programmer should know about time

Scroll to Top