PostgreSQL, pgAdmin, Java: How to make them all UTC?

Question

How can I make sure my entire development environment around PostgreSQL is not messing about with local timezones. For simplicity I need to be 100% sure that each and every time(stamp) value is UTC. When I inserted a row with timestamp without time zone (!) using the CURRENT_TIMESTAMP function I had to realize this was not the case, even though I never ever specified any time zone information.

Is there any step-by-step manual that helps me get rid of time zones?

I think that this is something which requires constant vigilance instead of a quick fix. — le3th4x0rbot, May 01 '13 at 23:26
That might be true. But if there existed a list of dangerous functions that might deal with other time zones than UTC, both SQL and Java, this would be very helpful. — ideaboxer, May 02 '13 at 20:29
Do all your server clocks happen to be set to UTC (and synced)? Timestamps with/without timezone are just a matter of representation, not storage format. If you're servers are all synced up, they will all use the same time frame. — That Realty Programmer Guy, Apr 20 '15 at 08:11

score 4 · Answer 1 · edited May 23 '17 at 12:34

4

This requires understanding first. I wrote a comprehensive answer about how PostgreSQL handles timestamps and time zones here:
Ignoring timezones altogether in Rails and PostgreSQL

You cannot "not" have a time zone. You can operate with the type timestamp [without time zone], but you'd still have a time zone in your client.

Your statement:

When I inserted a row with timestamp without time zone (!) using the CURRENT_TIMESTAMP function ...

is a contradictio in adjecto. CURRENT_TIMESTAMP returns a timestamp with time zone (!). If you just cast it (or have it coerced automatically) into timestamp [without time zone], the time zone offset is truncated instead of applied. You get local time (whatever the current time zone setting of the session is) instead of UTC. Consider:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
      ,CURRENT_TIMESTAMP::timestamp

Unless your local time zone setting is 'UTC' or something like 'London', the two expressions return different values.

If you want to save the literal value you see in your time zone, use one of:

SELECT CURRENT_TIMESTAMP::timestamp
      ,now()::timestamp
      ,LOCALTIMESTAMP;

If you want to save the point in time as it would be represented in UTC, use one of:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
      ,now() AT TIME ZONE 'UTC;

edited May 23 '17 at 12:34

Community

1
1

answered May 02 '13 at 00:46

Erwin Brandstetter

605,456
145
1,078
1,228

1

You are making things **much** more complicated than they need to be. Time stamps already do not contain any time zone information. They are simply the number of seconds since the epoch. Time zones are **only** used when converting a time stamp from/to a human-readable format. – Alvin Thompson May 02 '13 at 01:00
1

The problem arose from trying to save `CURRENT_TIMESTAMP` to a column of type `timestamp`. The current time zone setting is getting applied here, no amount of **bold** is going to change that. Try the sample code I supplied. I can assure you, I know what I am writing about. Find more explanation in the answer I linked to. – Erwin Brandstetter May 02 '13 at 01:28
Sir, **you are mistaken**! `CURRENT_TIMESTAMP` is (duh) a **timestamp**. That is, it's the number of seconds since the epoch. The number of seconds since the epoch for a given point in time is the same regardless of what time zone you're in. And just to be clear: you're implying that my comment couldn't be right, because I used bold? – Alvin Thompson May 02 '13 at 01:40
1

@AlvinThompson: [`CURRENT_TIMESTAMP`](http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) is a function in PostgreSQL that returns a value of type [`timestamp with time zone`](http://www.postgresql.org/docs/current/interactive/datatype-datetime.html). If there is a mistake in my answer I would ask you to point it out. I doubt there is one. – Erwin Brandstetter May 02 '13 at 01:48
That documentation is unfortunately worded, but it does support what I'm saying. The "with time zone"/"without time zone" types store the actual timestamps exactly the same way--the "with TZ" variety just additionally stores the time zone to be used **when converting the timestamp back to text**, instead of using the DBs default time zone. This has no effect on Java because the values are retrieved as time stamps. So that brings us back to my original statement: you're making things more complex than they need to be. – Alvin Thompson May 02 '13 at 03:09
@AlvinThompson CURRENT_TIMESTAMP is not relative to the epoch in postgres – That Realty Programmer Guy Apr 17 '15 at 01:44
1

@GaretClaborn: Contrary to what the somewhat misleadingly termed type name `timestamp with time zone` suggests, the time zone is not stored at all. It's not lost during the conversion to `text` on output (like Alvin suggests), it's never stored to begin with. Details [here](http://stackoverflow.com/questions/28872761/time-zone-storage-in-postgresql-timestamps/28876266#28876266) and [here](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170). – Erwin Brandstetter Apr 17 '15 at 02:43
@ErwinBrandstetter yes that's correct, it stores time using postgres internal timestamp format which get an attached timezone when displayed based on server timezone. I was merely commenting that the range for postgres **goes back further than a unix epoch-based timestamp's 1970**. (in response to `Sir, you are mistaken! CURRENT_TIMESTAMP is (duh) a timestamp. That is, it's the number of seconds since the epoch.`) which, is false. – That Realty Programmer Guy Apr 17 '15 at 10:10
1

@GaretClaborn: And you are right, of course. The matter is prone to misunderstandings. – Erwin Brandstetter Apr 17 '15 at 15:20
@GaretClaborn: Postgres timestamps don't use the "unix" epoch because postgres timestamps are 8 bytes instead of 4 and use nanosecond precision instead of millisecond precision. But postgres (like everyone) does indeed store its timestamps as the the amount of time from some arbitrary point in time. That arbitrary point in time is considered an "epoch" (more precisely the start of the epoch). An epoch is simply a (usually long) span of time that is considered distinct from the rest of tome in some way. – Alvin Thompson Apr 19 '15 at 09:40
@ErwinBrandstetter: I never said the time zone is lost when converting a timestamp to text. I said pretty much the opposite--that the time zone is used to convert the timestamp to/from text. And the construct `timestamp with time zone` is not misleading at all; it's used to specify that the literal to be converted to a timestamp (not the end product) contains time zone information. – Alvin Thompson Apr 19 '15 at 10:02
@ErwinBrandstetter: And I really don't understand why you're still arguing. You clearly now realize that I was correct because you pretty much copied my answer into your answer for the question you cited. – Alvin Thompson Apr 19 '15 at 10:14
@AlvinThompson I quoted your answer to clarify which portion of your comment was blatantly incorrect. Was there an argument? I simply stated a fact. Further, the arbitrary time is *not* considered the epoch. In fact, `epoch` is a predefined constant in postgres representing Jan 01, 1970 - like everything refering to epoch in the context of timestamps. You mean to refer to a postgres timestamp's low value, which is 4713 BC. This is not the timestamps "epoch", that's not how to use the term. – That Realty Programmer Guy Apr 20 '15 at 08:05
@GaretClaborn: My comment about quoting my answer wasn't addressed to you but rather Erwin. His answer [here](http://stackoverflow.com/questions/28872761/time-zone-storage-in-postgresql-timestamps/28876266#28876266) is effectively the same as my answer on this (and the referenced) page even though he disagrees with me on this page. I'll assume that your "blatantly wrong" statement was a result of this misunderstanding. :) – Alvin Thompson Apr 20 '15 at 17:40
@GaretClaborn: As for your statements about the use of the term "epoch", obviously [Erwin and at least 128 other people on this site disagree with you](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170). – Alvin Thompson Apr 20 '15 at 17:53
@AlvinThompson upvoting doesn't entail validating every fact; i'm not concerned with subjective data like that. the part which was blatantly wrong is where you've stated/re-stated what epoch is. so there's no further confusion (and so I wont have to check this anymore) you can see for yourself what an epoch is to postgres. http://www.postgresql.org/docs/9.2/static/functions-datetime.html – That Realty Programmer Guy Apr 20 '15 at 21:13
@GaretClaborn: First, there is no "subjective data" in a definition, so that statement (and the phrase "subjective data" itself ) makes little sense. Second, the documentation you site to prove your point actually proves the opposite. If you read it more carefully, you'll see that the `epoch` command produces two different values depending on how it's used: 1 Jan 1970 **UTC** for timestamps with a time zone and and 1 Jan 1970 **local time** for timestamps without a time zone. These are two distinct points in time, and the latter is (usually) **not** the unix epoch. – Alvin Thompson Apr 20 '15 at 21:53
@GaretClaborn: If you don't want to believe me or the 128 other people on this site, then simply look up the term "epoch" in a dictionary. If you need further convincing, Wikipedia [is pretty clear on what an epoch is in relation to computing](http://en.wikipedia.org/wiki/Epoch_(reference_date)#Computing). – Alvin Thompson Apr 20 '15 at 21:58
@AlvinThompson both are the unix epoch, jan 1 1970. just one using timestamptz. Looking at the wikipedia history, you're referencing user generated content that's been volatile in the last few weeks with notes about epoch dating lacking valid sources. You apparently need to feel right while this should be about not spreading misinformation. Regardless, we were discussing postgres's interpretation of epoch, which is not the base of their timestamp but the unix epoch. You can continue using subjective, poorly qualified data, but I'm done responding. – That Realty Programmer Guy Apr 21 '15 at 03:40
@GaretClaborn: Ok, you just proved that you have no idea what you're talking about. The [unix epoch](http://en.wikipedia.org/wiki/Unix_time) is always one single point in time: midnight on 1 Jan 1970 **UTC**. There are not multiple versions of the unix epoch depending on time zone. Think about what you're saying! That would mean that any point in time could be considered the unix epoch so long as you express it as 1 Jan 1970 with an offset, which is ridiculous. And now you're claiming that you're right and hundreds of people on this site, the dictionary, and wikipedia are all wrong. – Alvin Thompson Apr 21 '15 at 04:11
Guys, this has become a flame war. Please take it somewhere else or just leave it be. I'd rather not be notified with every new comment under my answer. – Erwin Brandstetter Apr 21 '15 at 04:16
@ErwinBrandstetter: It's not a flame war on my end. My motivation for challenging his assertions is that others who are searching for help on this issue might see his comments and think what he says is correct, since he seems so sure of himself. I won't allow who knows how many other people to wind up being misled so that he can pretend he knows what he's doing. – Alvin Thompson Apr 21 '15 at 06:22

score 2 · Answer 2 · edited May 23 '17 at 11:46

2

You have fallen victim to a major misconception: Time stamps do not contain any time zone information! See my other answer here for details. In other words, your entire development environment already doesn't use time zones. The only thing you need to ensure is that when a text representation of the time is converted to a time stamp (and vice versa), the thing doing the converting knows what time zone the text representation was expressed in. For everything else, time zones are irrelevant.

I blame Sun for this! They thought it would be convenient for developers if they included methods for converting a time stamp to/from text inside the timestamp object itself (first with Date and then with Calendar). Since this conversion required a time zone, they thought it would be extra convenient if that same class stored the time zone, so you wouldn't have to pass it every time when doing a conversion. This fostered one of the most pervasive (and damaging) misconceptions in Java ever. I don't know what excuse people who develop in other languages have. Maybe they're just dumb.

edited May 23 '17 at 11:46

Community

1
1

answered May 02 '13 at 00:54

Alvin Thompson

5,388
3
26
39

Thank you for clarification. In fact I have not fallen a victim to anything. I just complained about a time value not being UTC. Anyway, I expected time stamps to be stored in UTC since I am a Linux guy and it is good to know this is the case with PostgreSQL. – ideaboxer May 02 '13 at 20:39
When I say "stored in UTC" I actually mean seconds since the epoch or simply "the (world) time". – ideaboxer May 02 '13 at 20:50
"In fact I have not fallen a victim to anything. I just complained about a time value not being UTC." Once again, time values do **not** have time zone information. The time value is the same no matter what time zone it is displayed in; it is simply the number of seconds the epoch. – Alvin Thompson May 03 '13 at 14:16

score 0 · Answer 3 · answered May 01 '13 at 23:25

0

Declare date columns "timestamptz" or "timestamp with time zone".

Are you also asking about converting existing data not stored with timestamps?

answered May 01 '13 at 23:25

matthudson

182
6

I should clarify. The operative question for me is always, how do I want to get the data back? I think in this case, I'd want my data localized to my current time zone. But then, I might also want to arrange for others to view it localized to their time zone. If I don't store time zones then I can't convert it. Does this affect client programs? Not necessarily. You could find ways to mismatch the timezone contract between client and server, but it's also easy to write your views and functions to store time zones and return localized timestamps except where something else is specified. – matthudson May 02 '13 at 00:14
I am starting to introduce time handling into a new application. So there is no existing data and I try to avoid tinkering with wrong time values. Right now, all I need is UTC/seconds since the epoch or call it whatever you like, and nothing else. As easy as it could be. Stored the same way as displayed. For 2147483647 seconds since the epoch I need it to be displayed as Tue Jan 19 03:14:07. – ideaboxer May 02 '13 at 20:49

score 0 · Answer 4 · answered Apr 01 '20 at 20:34

Wrong type, use `TIMEZONE WITH TIME ZONE`

timestamp without time zone

The TIMESTAMP WITHOUT TIME ZONE data type in both Postgres and the SQL standard represents a date and a time-of-day but without any concept of time zone or offset-from-UTC. So this type cannot represent a moment, is not a point on the timeline.

Any time zone or offset information you submit with a value to a column of this type will be ignored.

When tracking specific moments, use the other type, TIMESTAMP WITH TIME ZONE. In Postgres, any time zone or offset information you submit with a value to a column of this type will be used to adjust into UTC (and then discarded).

For simplicity I need to be 100% sure that each and every time(stamp) value is UTC.

Then use a column of type TIMESTAMP WITH TIME ZONE.

s there any step-by-step manual that helps me get rid of time zones?

You do not want to get rid of time zones (and offsets), as that would mean you would be left with an ambiguous date and time-of-day. For example, noon on the 23rd of January this year fails to tell us if you mean noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US. Those are all different moments, all several hours apart.

java.time

With JDBC 4.2, we can exchanged java.time objects rather than the terrible legacy date-time classes.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

These values will all be in UTC, having an offset of zero hours-minutes-seconds.

Beware of middleware

Beware that many tools and middleware, such as PgAdmin, will lie to you. In a well-intentioned anti-feature, they apply a default time zone to the data pulled from the database. Values of type TIMESTAMP WITH TIME ZONE are always stored in UTC in Postgres, always. But your tool may report that value as if in America/Montreal, or Pacific/Auckland, or any other default time zone.

I recommend always setting the default time zone in such tools to UTC.

PostgreSQL, pgAdmin, Java: How to make them all UTC?

4 Answers4

Wrong type, use TIMEZONE WITH TIME ZONE

java.time

Beware of middleware

Wrong type, use `TIMEZONE WITH TIME ZONE`