Difference between timestamps with without time zone in PostgreSQL

Question

Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONE versus WITHOUT TIME ZONE   Can the differences be illustrated with simple test cases

User · Answer

Timestamptz vs Timestamp

The timestamptz field in Postgres is basically just the timestamp field where Postgres actually just stores the “normalised” UTC time, even if the timestamp given in the input string has a timezone.

If your input string is: 2018-08-28T12:30:00+05:30 , when this timestamp is stored in the database, it will be stored as 2018-08-28T07:00:00.

The advantage of this over the simple timestamp field is that your input to the database will be timezone independent, and will not be inaccurate when apps from different timezones insert timestamps, or when you move your database server location to a different timezone.

To quote from the docs:

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. To give a simple analogy, a timestamptz value represents an instant in time, the same instant for anyone viewing it. But a timestamp value just represents a particular orientation of a clock, which will represent different instances of time based on your timezone.

For pretty much any use case, timestamptz is almost always a better choice. This choice is made easier with the fact that both timestamptz and timestamp take up the same 8 bytes of data.

source: https://hasura.io/blog/postgres-date-time-data-types-on-graphql-fd926e86ee87/

User · Answer

The differences are covered at the PostgreSQL documentation for date time types  Yes  the treatment of TIME or TIMESTAMP differs between one WITH TIME ZONE or WITHOUT TIME ZONE  It doesn t affect how the values are stored  it affects how they are interpreted   The effects of time zones on these data types is covered specifically in the docs  The difference arises from what the system can reasonably know about the value    With a time zone as part of the value  the value can be rendered as a local time in the client  Without a time zone as part of the value  the obvious default time zone is UTC  so it is rendered for that time zone    The behaviour differs depending on at least three factors    The timezone setting in the client  The data type  i e  WITH TIME ZONE or WITHOUT TIME ZONE  of the value  Whether the value is specified with a particular time zone    Here are examples covering the combinations of those factors   foo  gt  SET TIMEZONE TO  Japan   SET foo  gt  SELECT  2011-01-01 00 00 00   TIMESTAMP        timestamp       ---------------------  2011-01-01 00 00 00  1 row   foo  gt  SELECT  2011-01-01 00 00 00   TIMESTAMP WITH TIME ZONE        timestamptz        ------------------------  2011-01-01 00 00 00 09  1 row   foo  gt  SELECT  2011-01-01 00 00 00 03   TIMESTAMP        timestamp       ---------------------  2011-01-01 00 00 00  1 row   foo  gt  SELECT  2011-01-01 00 00 00 03   TIMESTAMP WITH TIME ZONE        timestamptz        ------------------------  2011-01-01 06 00 00 09  1 row   foo  gt  SET TIMEZONE TO  Australia Melbourne   SET foo  gt  SELECT  2011-01-01 00 00 00   TIMESTAMP        timestamp       ---------------------  2011-01-01 00 00 00  1 row   foo  gt  SELECT  2011-01-01 00 00 00   TIMESTAMP WITH TIME ZONE        timestamptz        ------------------------  2011-01-01 00 00 00 11  1 row   foo  gt  SELECT  2011-01-01 00 00 00 03   TIMESTAMP        timestamp       ---------------------  2011-01-01 00 00 00  1 row   foo  gt  SELECT  2011-01-01 00 00 00 03   TIMESTAMP WITH TIME ZONE        timestamptz        ------------------------  2011-01-01 08 00 00 11  1 row

User · Answer

Here is an example that should help  If you have a timestamp with a timezone  you can convert that timestamp into any other timezone  If you haven t got a base timezone it won t be converted correctly   SELECT now       now    timestamp     now   AT TIME ZONE  CST      now    timestamp AT TIME ZONE  CST    Output   -  RECORD 1  --------------------------- now        2018-09-15 17 01 36 399357 03 now        2018-09-15 17 01 36 399357 timezone   2018-09-15 08 01 36 399357 timezone   2018-09-16 02 01 36 399357 03

User · Answer

I try to explain it more understandably than the referred PostgreSQL documentation   Neither TIMESTAMP variants store a time zone  or an offset   despite what the names suggest  The difference is in the interpretation of the stored data  and in the intended application   not in the storage format itself    TIMESTAMP WITHOUT TIME ZONE stores local date-time  aka  wall calendar date and wall clock time   Its time zone is unspecified as far as PostgreSQL can tell  though your application may knows what it is   Hence  PostgreSQL does no time zone related conversion on input or output  If the value was entered into the database as  2011-07-01 06 30 30   then no mater in what time zone you display it later  it will still say year 2011  month 07  day 01  06 hours  30 minutes  and 30 seconds  in some format   Also  any offset or time zone you specify in the input is ignored by PostgreSQL  so  2011-07-01 06 30 30 00  and  2011-07-01 06 30 30 05  are the same as just  2011-07-01 06 30 30   For Java developers  it s analogous to java time LocalDateTime  TIMESTAMP WITH TIME ZONE stores a point on the UTC time line  How it looks  how many hours  minutes  etc   depends on your time zone  but it always refers to the same  physical  instant  like the moment of an actual physical event   The input is internally converted to UTC  and that s how it s stored  For that  the offset of the input must be known  so when the input contains no explicit offset or time zone  like  2011-07-01 06 30 30   it s assumed to be in the current time zone of the PostgreSQL session  otherwise the explicitly specified offset or time zone is used  as in  2011-07-01 06 30 30 05    The output is displayed converted to the current time zone of the PostgreSQL session  For Java developers  It s analogous to java time Instant  with lower resolution though   but with JDBC and JPA 2 2 you are supposed to map it to java time OffsetDateTime  or to java util Date or java sql Timestamp of course     Some say that both TIMESTAMP variations store UTC date-time  Kind of  but it s confusing to put it that way in my opinion  TIMESTAMP WITHOUT TIME ZONE is stored like a TIMESTAMP WITH TIME ZONE  which rendered with UTC time zone happens to give the same year  month  day  hours  minutes  seconds  and microseconds as they are in the local date-time  But it s not meant to represent the point on the time line that the UTC interpretation says  it s just the way the local date-time fields are encoded   It s some cluster of dots on the time line  as the real time zone is not UTC  we don t know what it is

[postgresql] Difference between timestamps with/without time zone in PostgreSQL

Examples related to postgresql

Examples related to types

Examples related to timestamp

Examples related to timezone