Hacker News Reader

I'm not sure why this is necessary. It's a very light wrapper around existing SQL functions that adds additional dependencies and makes it more difficult for developers who already know SQL.

Maybe the use case is for installable packages that would be used with different backend databases... but in general, it seems like a bad idea.

View on HN

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.

Reply on news.ycombinator.com