3 ms·

The blog post and this comment are oriented towards direct commandline use of PostgreSQL, and to the extent you want to use the database as your primary front f

by zzzeek 8y ago

The blog post and this comment are oriented towards direct commandline use of PostgreSQL, and to the extent you want to use the database as your primary front facing programming API, you need all the conveniences of opinionated types and conversion functions. But when the database is in its more traditional role as persistence for a large layer of business and presentation logic running in a front facing process, having a stricter model with less chance of implicit decisions made by the database itself is better. I think that's part of the disconnect here why some people are downmodding like crazy while others are agreeing. People are coming from two different perspectives, perhaps data scientists vs backend engineers.

jeltz 8y ago
My background is a backend engineer and I would never use SQL for presentation (outside the occasional custom reporting query), but that does not mean you should not use timestamptz. You should just use a database driver which treats a timstamptz as an absolute point in time and throws away the time zone after parsing. By using timestamptz with a good PostgreSQL driver you ensure that PostgreSQL's own timefunctions work as expected while also getting sane data types from your queries, data types where time zone should be handled in the presentation layers. In the industries I have worked in aggregating events per date (according to some time zone) has been a common operation and that would have been error prone with the timestamp type.
- zzzeek 8y ago
  I think there are mistakes to be made with the use of either type. On the Python side using "timestamp with time zone" means we're going to get back timezone-aware datetime objects, which are perfectly fine but they need to be handled correctly on the application side. I would blame the implicit decisions made by PostgreSQL of operators like "AT TIME ZONE", choosing to make the same operator do two different things based on the flavor of timestamp as well as returning the opposite type of type from that given that makes the whole area more difficult than it has to be. On MySQL, the convert_tz() function is clear and unambiguous, you pass it the timestamp, and the "from" and "to" timezones. The difference is that PostgreSQL is trying to be an "Object relational database" with a lot of SQL language complexity whereas MySQL is just trying to be a persistence engine.

by zzzeek 8y ago

jeltz 8y ago

My background is a backend engineer and I would never use SQL for presentation (outside the occasional custom reporting query), but that does not mean you should not use timestamptz. You should just use a database driver which treats a timstamptz as an absolute point in time and throws away the time zone after parsing. By using timestamptz with a good PostgreSQL driver you ensure that PostgreSQL's own timefunctions work as expected while also getting sane data types from your queries, data types where time zone should be handled in the presentation layers. In the industries I have worked in aggregating events per date (according to some time zone) has been a common operation and that would have been error prone with the timestamp type.

zzzeek 8y ago
I think there are mistakes to be made with the use of either type. On the Python side using "timestamp with time zone" means we're going to get back timezone-aware datetime objects, which are perfectly fine but they need to be handled correctly on the application side. I would blame the implicit decisions made by PostgreSQL of operators like "AT TIME ZONE", choosing to make the same operator do two different things based on the flavor of timestamp as well as returning the opposite type of type from that given that makes the whole area more difficult than it has to be. On MySQL, the convert_tz() function is clear and unambiguous, you pass it the timestamp, and the "from" and "to" timezones. The difference is that PostgreSQL is trying to be an "Object relational database" with a lot of SQL language complexity whereas MySQL is just trying to be a persistence engine.