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

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

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

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

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

[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