3 ms·

Thanks for the feedback. Working correctly with time zones in the database can be tricky. DATE_TRUNC('day', $1 AT TIME ZONE $2) AT TIME ZONE $2; or CONVERT_

by akane 13y ago

Thanks for the feedback. Working correctly with time zones in the database can be tricky. DATE_TRUNC('day', $1 AT TIME ZONE $2) AT TIME ZONE $2; or CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-%m-%d 00:00:00'), time_zone, '+00:00'); are not very intuitive (or aren't to me at least).

tudborg 13y ago
Simply use and store everything in UTC. If you ever need to do grouping by time in any other timezone, it would be trivial to do the conversion. DAYOFYEAR( CONVERT_TZ(my_datetime_field,'+00:00', '+02:00') ) Seems very intuitive to me.