
Timestamps and Time Zones in PostgreSQL (2016) - GordonS
https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/
======
msla
I'd be happy with everyone being extremely clear about the two kinds of time:
Wallclock-time and duration-time.

What's the difference? Duration-time does not have leap-seconds.

I'll say it again: Duration-time does not have leap-seconds.

Once more: Duration-time does not have leap-seconds.

For duration-time without leap-seconds, give me TAI:

> International Atomic Time (TAI) is a statistical atomic time scale based on
> a large number of clocks operating at standards laboratories around the
> world that is maintained by the Bureau International des Poids et Mesures;
> its unit interval is exactly one SI second at sea level. The origin of TAI
> is such that UT1-TAI is approximately 0 (zero) on January 1, 1958. TAI is
> not adjusted for leap seconds. It is recommended by the BIPM that systems
> which cannot handle leapseconds use TAI instead.

[https://tycho.usno.navy.mil/leapsec.html](https://tycho.usno.navy.mil/leapsec.html)

Document that it is TAI.

Include in the documentation that TAI does not include leap-seconds.

Thank you.

~~~
gsich
Why do you call it "duration-time"? It seems obvious that for an event that
lasted 5.43s for example, (a "duration", better wording) that it's not counted
with leap-seconds.

~~~
msla
> Why do you call it "duration-time"?

Because it has units of time (seconds, often) and it's done using time-stamps,
as in begin-time and end-time.

> It seems obvious that for an event that lasted 5.43s for example, (a
> "duration", better wording) that it's not counted with leap-seconds.

Obvious until you try to get someone to understand why their time-stamps can't
be UTC if they're going to be used for duration-time instead of wallclock-
time.

Obvious until you try to get a library or language implementer to understand
the UTC isn't Universal.

~~~
gsich
>Because it has units of time (seconds, often) and it's done using time-
stamps, as in begin-time and end-time.

If you are recording a duration you have another unit. If you use begin-time
and end-time you are calculating a duration, which is different of course.

If you calculate durations, then you might consider leap seconds, if they are
important for your measurement. If not, then you just ignore them. I don't see
a problem here.

Besides, leap-seconds are known, so removing them from a calculated duration
is not a problem.

------
zeroimpl
I sometimes wish there was an actual "TIMESTAMP WITH TIME ZONE" type that
stored the input timestamp as well. Basically, a version that didn't
canonicalize the input by converting to UTC. This way, I can easily show
timestamps to the user in the time zone they used to input them.

~~~
niftich
I frequently wish that datetime libraries and APIs frozen in time and awkward
design were given a second pass and redone in the style of Java 8 [1], which
is a triumph of clarity after many years of lessons learned. Even putting out
a new RFC would help move things along, because I've long felt that RFC 3339
is underspecified [2].

If datetime libraries were more thoroughly patterned after that of Java 8,
there would be less need for the 'misconceptions programmers believe about
datetimes' posts, and less need for the 'just use UTC' or 'maybe always-UTC
isn't the right advice' posts, because the data-types are thoroughly
reflective of their intended use, and their misuse is harder than in other
APIs.

[1]
[https://docs.oracle.com/javase/8/docs/api/java/time/package-...](https://docs.oracle.com/javase/8/docs/api/java/time/package-
summary.html) [2]
[https://news.ycombinator.com/item?id=12364805](https://news.ycombinator.com/item?id=12364805)

~~~
twic
Am i right in thinking that in Java 8 terms:

SQL timestamp == java.time.LocalDateTime

SQL timestampz == java.time.Instant, but converted into
java.time.OffsetDateTime on read

?

That is, neither of them actually stores a timezone. But timezone is a date
and time that needs to be interpreted in the context of some separately-
supplied timezone, whereas timezonetz is an actual instant in time?

IME, it's rare to need LocalDateTime. I work in finance. I often need
LocalTime, to specify things like "the exchange opens at 07:30 every day",
where the timezone is implicitly that of the exchange, and if the exchange
moved (lol Brexit), its timezone might change even if its opening time didn't.
I also often need LocalDate, mostly when storing or retrieving data in
archives organised by date, because we tend to use the local date for that, so
that all the data for one session of an exchange, from open to close, is on
one date, wherever in the world it is [1]. But i very rarely need
LocalDateTime. Perhaps if i worked in calendaring instead, i would use it all
the time.

It's a shame that SQL doesn't have a ZonedDateTime or OffsetDateTime, but as
others have pointed out, you can just store a timestamp along with a timezone
or offset.

[1] Actually, it's worse than this, in that we use the trade date, which is
sometimes not the same as the local date - for example, on the Chicago
Mercantile Exchange, the market for the E-mini S&P 500 future opens at 17:00
and closes at 16:00 the next day [2]. But that entire session is considered to
have a trade date of the calendar date it closes on. In the olden days, the
market would open at 07:00 and close at 16:00 (so you had an hour before the
banks closed to settle any cash movements, or possibly because traders had
very big lunches in those days). Trade date was calendar date. But then the
21st century came along, and they decided to open all day. But because there
was so much crucial stuff that happened at the close of trading, they did it
by moving the open back to the previous evening, rather than moving the close
forward.

[2] [https://www.cmegroup.com/trading-
hours.html#equityIndex](https://www.cmegroup.com/trading-
hours.html#equityIndex)

~~~
niftich
PostgreSQL 'TIMESTAMP', for nonextreme values, is the same idea as
java.time.Instant -- an absolute point on the timeline stored internally as a
number from some epoch -- but whose default textual interface accepts and
emits java.time.LocalDateTime values that correspond, but aren't tagged with
the UTC zone.

When you insert into a 'TIMESTAMP', it's as if it did input
LocalDateTime(x).atZone(UTC).toInstant(), and saved that; and when you read
it, it's as if it does storedInstant.atZone(UTC).toLocalDateTime().

A 'TIMESTAMPTZ' is stored the same way, but on string output is converted to
the session timezone, and an underspecified input is enriched with the session
timezone.

When you insert into a 'TIMESTAMPTZ' it'd take the input OffsetDateTime or
ZonedDateTime, or enrich the input with the session zone until one were
obtained, call .toInstant(), and save it. On read, it'd do
storedInstant.atZone(sessionZone) to obtain a ZonedDateTime, and its default
output format only includes the offset.

LocalDateTime is useful for calendaring, and as an intermediate step when
joining a LocalDate and a LocalTime and working towards a Zoned or Instant.

------
mjevans
I think I slightly disagree about the choice of using timestamptz over
timestamp. The main reason being that when I feed data in to the database I
always want that same data back out. From the description I now realize that
if I backed up and restored, or otherwise ran the same database with a
different configuration the database might "try to be smart" and return a
modified value.

I guess to store a timestamp and a timezone I should use a timestamp a second
column with the timezone value (maybe a string, but I'd like a clean way of
telling the database I want an 'enum' but to dynamically just add new values
as it sees them; yes for PostgreSQL I'd do that with a secondary lookup table
and an insert/update trigger).

~~~
minitech
It would return an equivalent value, which is the important thing. You can
specify `AT TIME ZONE 'UTC'` (or whichever timezone) when consuming it to get
a consistent look. On the other hand, it’s not clear from just the value what
point in time a `timestamp without time zone` represents. That’s why I agree
that it should be used when you _don’t_ want to represent a point in time.

~~~
colanderman
Moreover, `AT TIME ZONE` only works correctly when used following the
semantics described in the article. It will do the opposite of what you think
it should if you use `timestamp without time zone` to represent an absolute
time, or vice-versa.

------
zzzeek
I completely disagree with this advice:

> However, when you come across timestamp, you can’t possibly know in what
> time zone the timestamp is just by looking at it.

yes I do, it's in UTC, because I assume the application is written to
correctly handle datetimes.

> It is merely a wall time. Maybe it’s UTC,

no, it's UTC.

> or maybe the developer stored it in local time.

right, maybe the application was not programmed correctly, that is always a
possibility. But if they are using the timestamp with timezone datatype, it is
100% more likely they have made mistakes in doing so because it is much more
confusing to have to fight against PostgreSQL's assumptions and implicit
conversions, as the complexity of its behaviors and decisions using this
datatype are described in this article. Does the application come up with the
wrong answer if the server it runs on, or the webserver container, is
mistakenly set to Eastern Timezone rather than UTC? Is the application
ensuring that it knows the correct timezone of timestamps _as they enter the
application_ , and if so, why isn't it converting to UTC _at that moment_? Is
the application otherwise receiving datetimes of arbitrary timezone, letting
them move around business and display logic in their raw form, and waiting
until they enter _the database_ to be correctly normalized? that's too late.

A correctly coded application does _explicit_ normalization of datetime values
the moment they enter the boundaries of the application, just like it would
also convert encoded bytes into a unicode-aware strings up front, so that
within the application there is no ambiguity, both when persisting and when
handling.

~~~
djrobstep
Why is this correct take being downvoted?

Add consistency to your codebase by saving all timestamps in UTC.

Edited: Storage size is the same.

~~~
grzm
There's no difference in storage between timestamp and timestamptz in
PostgreSQL. Both are 8 bytes.

------
manigandham
As many other comments state, the most practical advice is to just use _'
timestamp'_ and ensure that all applications always use UTC. The only
exception is user-facing appointments that are scheduled in that user's
timezone which can't be converted to UTC safely. In this case, store the
timezone in a separate column specifically for those rows/entities that need
it.

In neither case is _timezonetz_ useful or necessary, and will actually end up
causing more bugs by converting timezones to/from the database instead of just
returning the value and letting your application deal with it.

~~~
taffer
I think you misunderstood how timestamps work in PostgreSQL. Timestamptz is
always saved as UTC. Only in exceptional cases where you need to store
something in local time, such as a scheduled event that is not in UTC, you
should use the timestamp WITHOUT TZ. This is also what well-known PostgreSQL
developers recommend: [https://tapoueh.org/blog/2018/04/postgresql-data-types-
date-...](https://tapoueh.org/blog/2018/04/postgresql-data-types-date-
timestamp-and-time-zones/)

~~~
manigandham
It's not about the storage of the value, it's the fact that PG will
potentially change the value you provide compared to what's stored when using
'timestamptz'. If you use UTC _and_ send it as UTC _and_ request it as UTC
then everything is fine but there are plenty of edge cases when using ORMs,
handwritten SQL, different server timezones, or type conversions that can lead
to issues on the way in and out of the database.

Having the database store anything other than what you provide is always a
source of bugs.

------
gigatexal
What I like about this article is that it does something subtle yet profound:
it uses repetition to solidify a concept to the reader. For example the
referencing of wall time in the first few paragraphs lowers the load for the
reader to refer back to previous paragraphs to reconnect with a newly
introduced concept (walltime in this context was new to me as of reading this
article). I think this is a huge win as it doesn’t break the reading timeline
and it’s a subtle yet huge showing of attention to detail. Much kudos to the
author.

~~~
fphilipe
Thanks, much appreciated.

At least for me, writing an article like that takes time and lots of thought
(plus forces you to grok the stuff deeply). I wish I could write more often
(or at least, carve out the time and energy for it), haven’t written much
since.

~~~
yosamino
And thank you so much for the chart[0] mapping absolute to wall time. I always
have a hard time visualizing DST changes ( usually I am mentally "sliding"
bars of time against each other and get confused), but that one just gave me a
_click_.

[0] [https://phili.pe/posts/timestamps-and-time-zones-in-
postgres...](https://phili.pe/posts/timestamps-and-time-zones-in-
postgresql/map.svg)

------
jasonl99
That is a phenomenal article about timezones in general that apply to any
database, as well as a great explanation of features specific to postgres.

It is interesting that a timestamp without timezone is internally represented
a timestamptz at UTC.

Postgres is also has some great functionality converting between timezones:

[https://www.postgresql.org/docs/11/functions-
datetime.html#F...](https://www.postgresql.org/docs/11/functions-
datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)

------
sudhirj
Doesn’t this mean that the default Rails treatment of time stamps (created and
updated at) in Postgres is wrong? Any plans to change that?

------
robbiep
I have a question, that was partially addressed in some comments in the last
Timezone in Databases article earlier in the week -

What do people think about using Unix time in databases?

~~~
TheAceOfHearts
It depends on your use-case. You should also be aware of the Year 2038 problem
[0].

What are you trying to achieve?

[0]
[https://en.wikipedia.org/wiki/Year_2038_problem](https://en.wikipedia.org/wiki/Year_2038_problem)

~~~
robbiep
Largely timestamping. Accuracy to a high level (leap seconds) is irrelevant.
Aware of 2038 problem, assume we will have some fun along the way to there
along with the rest of the unix world!

~~~
TheAceOfHearts
Storing Unix timestamps in a 64-bit signed integer field would probably be
fine in that situation. The only downsides that come to mind are that you'd
have to manually convert the number to an actual date when using it, and that
you'd lose easy access to date-related functionality in the database.

~~~
jeltz
But also totally pointless since PostgreSQL's timestamptz type also uses 64
bits.

------
andynrkri
Thanks for this. Was very helpful!

------
cpeterso
What timestamp types are available in MySQL, SQL Server, and Oracle?

~~~
userbinator
SQL Server has datetimeoffset, which computes in UTC but also stores the
timezone.

~~~
crooked-v
Does it store the time zone, or does it store the offset?

~~~
GordonS
AFAIK, it stores the offset

------
welder
> A Valid Use Case For timestamp without time zone

This should be "A Valid Use Case For timestamp _with_ time zone".

This article gets it all wrong. The only time it's acceptable to store time
with time zone is DATES that do not have a TIME component.

~~~
groestl
I'm probably just misunderstanding, but a calendar like Google Calendar
definitely needs to store time with a time zone (i.e. dates with a time
component). This allows to keep a scheduled event at 9 AM, even if the time
zone offset is redefined (due to a political event).

~~~
alboy
Or some of the schedulees of the events move across time zones. There're
absolutely other use cases.

