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.