Hacker News Reader

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).

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.