3 ms·

I did a full switch to Postgres like 2 years ago, and a decent chunk of the decision was `timestamp with timezone` saved me a huge amount of headaches working o

I did a full switch to Postgres like 2 years ago, and a decent chunk of the decision was `timestamp with timezone` saved me a huge amount of headaches working on multinational services Use timestamp with tz, drop Unix timestamps and use ISO8601 DateTime format across the application ezpz never look back

benmmurphy 8y ago
I think PG will exhibit the problems that skeet was referring to because PG stores the UTC time and not the local time. If someone makes a change to the timezone rules then the times your columns are referring to will stay the same in UTC time which might not be what you want. https://www.postgresql.org/docs/9.1/datatype-datetime.html https://www.postgresql.org/docs/9.1/datatype-datetime.html "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone." 'timestamp with timezone' is kind of a gotcha. i guess it combines two things into one type that are logically one type but it also might mislead someone into believing the type is something else.
emilsedgh 8y ago
Unfortunately `timestamp with time zone` is misleading and crippled. When you store something as timestamptz, postgres converts it to UTC and "loses" the timezone part. The only difference between timestamptz and timestamp is that when postgres is returning the timestamptz to you, it converts it back to your local time zone defined by SET TIME ZONE (and not the time zone it was originally stored in) So basically if you store a timestamptz, you cannot convert it back to the local time, as the local time is never even stored. I personally had to add a second `timezone` column to my table to store the timezone of the date.
- colanderman 8y ago
  I wouldn't go so far as to say the timestamp types are broken, just confusingly named. `timestamp with time zone` signifies an absolute point in time; `timestamp without time zone` signifies a timestamp relative to some unspecified time zone (which you must store separately, as you point out). The builtin functions universally adhere to these semantics. E.g., when you do store the timezone separately, use `timestamp without time zone`: `time AT TIME ZONE timezone` [1] only does the right thing (i.e., interpreting `time` as relative to `timezone`, and producing an absolute time corresponding to it) if `time` is `without time zone`. (If `time` is `with time zone`, it produces the local time corresponding to the absolute time `time`, which is mathematically exactly the opposite of what you want.) [1] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT https://www.postgresql.org/docs/current/functions-datetime.h...
  - emilsedgh 8y ago
    I said they are crippled, not broken. Unfortunately the poor naming is a big faux pas. I really really expected timestamp with time zone to store the time zone.
funcDropShadow 8y ago
Additionally, you have to be careful with the Postgres JDBC driver. When you store or load to a timestamp column, it compares the JVM local time zone with Postgres' session time zone and translates appropriately. Even when you have a test that makes sure that you get back the same timestamp that you put into it, it might be the wrong timestamp if you use psql to look at it.
ams6110 8y ago
Unix timestamps are almost always the wrong choice to represent date/time, unless you're doing something directly involving a unix filesystem.
snuxoll 8y ago
timstamptz is fine for historical data and certain kinds of “this will (need to )happen at this time” situations, but as others have said it just converts to/from UTC based on your session time zone - this makes it inadequate for calendaring. For calendaring or other applications where time zone’s shifting offsets can cause issues you need to store the UTC time along with the timezone and then do the conversion on read.

benmmurphy 8y ago

I think PG will exhibit the problems that skeet was referring to because PG stores the UTC time and not the local time. If someone makes a change to the timezone rules then the times your columns are referring to will stay the same in UTC time which might not be what you want. https://www.postgresql.org/docs/9.1/datatype-datetime.html https://www.postgresql.org/docs/9.1/datatype-datetime.html "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone." 'timestamp with timezone' is kind of a gotcha. i guess it combines two things into one type that are logically one type but it also might mislead someone into believing the type is something else.

emilsedgh 8y ago

Unfortunately `timestamp with time zone` is misleading and crippled. When you store something as timestamptz, postgres converts it to UTC and "loses" the timezone part. The only difference between timestamptz and timestamp is that when postgres is returning the timestamptz to you, it converts it back to your local time zone defined by SET TIME ZONE (and not the time zone it was originally stored in) So basically if you store a timestamptz, you cannot convert it back to the local time, as the local time is never even stored. I personally had to add a second `timezone` column to my table to store the timezone of the date.

colanderman 8y ago
I wouldn't go so far as to say the timestamp types are broken, just confusingly named. `timestamp with time zone` signifies an absolute point in time; `timestamp without time zone` signifies a timestamp relative to some unspecified time zone (which you must store separately, as you point out). The builtin functions universally adhere to these semantics. E.g., when you do store the timezone separately, use `timestamp without time zone`: `time AT TIME ZONE timezone` [1] only does the right thing (i.e., interpreting `time` as relative to `timezone`, and producing an absolute time corresponding to it) if `time` is `without time zone`. (If `time` is `with time zone`, it produces the local time corresponding to the absolute time `time`, which is mathematically exactly the opposite of what you want.) [1] https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT https://www.postgresql.org/docs/current/functions-datetime.h...
- emilsedgh 8y ago
  I said they are crippled, not broken. Unfortunately the poor naming is a big faux pas. I really really expected timestamp with time zone to store the time zone.

funcDropShadow 8y ago

Additionally, you have to be careful with the Postgres JDBC driver. When you store or load to a timestamp column, it compares the JVM local time zone with Postgres' session time zone and translates appropriately. Even when you have a test that makes sure that you get back the same timestamp that you put into it, it might be the wrong timestamp if you use psql to look at it.

ams6110 8y ago

Unix timestamps are almost always the wrong choice to represent date/time, unless you're doing something directly involving a unix filesystem.

snuxoll 8y ago

timstamptz is fine for historical data and certain kinds of “this will (need to )happen at this time” situations, but as others have said it just converts to/from UTC based on your session time zone - this makes it inadequate for calendaring. For calendaring or other applications where time zone’s shifting offsets can cause issues you need to store the UTC time along with the timezone and then do the conversion on read.