Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question
1  
I think that this is something which requires constant vigilance instead of a quick fix. –  Bailey S May 1 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. –  user1625837 May 2 at 20:29
add comment

3 Answers

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, the time zone offset is truncated instead of applied. 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;
share|improve this answer
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 2 at 1:00
 
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 2 at 1: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 2 at 1:40
1  
@AlvinThompson: CURRENT_TIMESTAMP is a function in PostgreSQL that returns a value of type timestamp with time zone. If there is a mistake in my answer I would ask you to point it out. I doubt there is one. –  Erwin Brandstetter May 2 at 1: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 2 at 3:09
add comment

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.

share|improve this answer
 
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. –  user1625837 May 2 at 20:39
 
When I say "stored in UTC" I actually mean seconds since the epoch or simply "the (world) time". –  user1625837 May 2 at 20:50
 
I learned something, thank you! –  Bailey S May 2 at 22:44
 
"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 3 at 14:16
add comment

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

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

share|improve this answer
 
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 2 at 0: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. –  user1625837 May 2 at 20:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.