My head is starting too spin with timestamp and timezone issues. In my app, all timestamps are saved as UTC. The timezones of where it was submitted is saved separately.
The environment is as follows:
The first issue I solved was the fact that PostgreSQL default timezone was set to America/Eastern on a server in San Francisco. So I switched to UTC, but that was only part of the problem and I'm not 100% sure that even did anything. FYI, the column in PostgreSQL is TIMESTAMP WITH TIME ZONE.
The localized date for the tests, i.e. MST or UTC-07:
The UTC date used in the insert for all the tests:
First Test:----------------------------------------------
With a quick SQL insert, the right date will go in. When I query, I get the localized date because my local system offsets it by -7 hours to MST.
The date after query:
This is indeed what time I inserted locally.
Second Test:----------------------------------------------
Here is where it gets weird. Now I input the date through the app using the same UTC timestamp as in the SQL insert.
Now look what comes out when I query:
If inserted with raw SQL, the date is subtracted by 7 hrs to get my local time. If my local machine is subtracting 7 from the original UTC timestamp, then the original UTC timestamp that comes back with the app would be plus 7 hrs giving us:
Instead of what I put in. Huh . . . ?
PostgreSQL seems to have made it off the suspect list because of the successful SQL inserts and the timezone change to UTC. This means the culprit might be one of the following:
My plan is to methodically change anything and everything on the server to default to UTC timezone. Does anyone have any idea as to what is going on here? Is my plan to default everything to UTC even a good idea?
Let's pull this apart piece by piece.
I am not sure of your problem but I suspect you are confused by the misleading behavior of Postgres’ presentation of date-time values in a console session, described below.
In Postgres you should be using the TIMESTAMP WITH TIME ZONE, virtually never TIMESTAMP WITHOUT TIME ZONE. Study the Postgres doc carefully. For the WITH type, Postgres uses any time zone or offset-from-UTC provided with the incoming data to adjust the given date-time to UTC. Postgres always stores TIMESTAMP WITH TIME ZONE in UTC. The passed time zone or offset info is forgotten after being used to make the adjustment to UTC. This behavior may differ from other database systems; as far as I know the SQL spec does not specify such details.
When querying via a SQL console, the text generated to represent the date-time value in the TIMESTAMP WITH TIME ZONE will have the SQL session’s current time zone applied as part of the text generation. This can be misleading as it does not mean the stored data was in that time zone. In actuality, the TIMESTAMP WITH TIME ZONE was stored in UTC, always. Experiment in your console to see for yourself, as shown below. Keep in mind that all the outputs you see below represent virtually the same moment (I ran all four in quick succession), but their wall-clock time is quite different.
First use the default time zone established for your interactive SQL session. For me on my development workstation that is America/Los_Angeles (west coast of United States), with an offset-from-UTC of -08:00.
SELECT now(); -- Or query your `TIMESTAMP WITH TIME ZONE` column.
2016-01-27 12:16:35.681057-08
Try another time zone, this one towards western Canada, in Alberta, named America/Edmonton, with an offset of -07:00 in standard time and -06:00 in DST.
SET TIME ZONE 'America/Edmonton'; -- Offset from UTC: -07:00
SELECT now(); -- Or query your `TIMESTAMP WITH TIME ZONE` column.
2016-01-27 13:17:01.502281-07
Try a time zone ahead of UTC rather than behind it. The offset is + rather than -. Also notice that India is five and a half hours ahead. Not all time zones have full-hour increments in their offset. So in this example the minute-of-hour is 47 rather than the 17 seen above.
SET TIME ZONE 'Asia/Kolkata'; -- Offset from UTC: +05:30
SELECT now(); -- Or query your `TIMESTAMP WITH TIME ZONE` column.
2016-01-28 01:47:28.95779+05:30
Lastly, try UTC. This means an offset of +00:00, as all other time zones are defined against UTC. Sometimes you will see Z (short for Zulu) which also means UTC.
SET TIME ZONE 'UTC';
SELECT now(); -- Or query your `TIMESTAMP WITH TIME ZONE` column.
2016-01-27 20:17:50.86757+00
In JDBC, you should be calling getTimestamp on your ResultSet to get a java.sql.Timestamp. A java.sql.Timestamp is always in UTC.
Annoyingly, and confusingly, its toString method silently applies your JVM’s current default time zone when generating the String that is a textual representation of the date-time value. So it may seem like it has a time zone while in fact it does not. These old date-time classes were well-intentioned but made some unfortunate design choices such as this behavior. Another reason to convert to java.time types ASAP.
From java.sql types you should convert to the java.time types built into Java 8 and later. Then proceed to your business logic.
The basic java.time types are… An Instant is a moment on the timeline in UTC. Get an Instant by calling toInstant on your java.sql.Timestamp. Most of your work should be in UTC, so work often with Instant. Apply a time zone (ZoneId) to get a ZonedDateTime when you need to present to the user in their expected/desired time zone.
In all your java.time operations, always specify the desired/expected time zone. If omitted, the JVM’s current default time zone is silently applied. Why do I say “current”? Because your JVM’s default time zone can change at any moment, even during runtime(!). Do not rely implicitly on the default, be explicit. Ditto for Locale, but that is another discussion.
Avoid the old java.util.Date/.Calendar classes bundled with early versions of Java. They are notoriously troublesome. They have been supplanted by java.time classes.
Notice that going from TIMESTAMP WITH TIME ZONE in Postgres through JDBC to java.sql.Timestamp to a java.time Instant means everything is in UTC. The time zone of the database server hardware/OS does not matter. The time zone of the SQL session does not matter. The time zone on the Java Virtual Machine’s host server hardware/OS does not matter. The current default time zone in the JVM does not matter.
What does matter is that you verify the hardware clock on your database server and on your JVM host are both correct, set to the correct time appropriate to their assigned time zone. While it is generally best practice to set your all your servers’ OS (and Postgres and server JVM) to UTC, given the scenario described above it is not required.
I believe the PostgreSQL JDBC driver is trying to offset a UTC timestamp based on the JVM which asks the server/OS for it's default.
No, your belief is not correct. The Postgres-provided JDBC driver does not apply the JVM’s current default time zone for java.sql.Timestamp. Such application would be irrelevant when going from UTC in the stored database date-time value to the java.sql.Timestamp value which is also in UTC.
Use proper time zone names. They come in the format of Continent/Region. Never use 3-4 letter codes like MST as they are neither standardized nor unique. If you meant Mountain Standard Time, use something like America/Edmonton or America/Denver.
Using JDBC with Postgres simplifies date-time handling, to my mind. Moving to and from:
TIMESTAMP WITH TIME ZONE↔java.sql.Timestamp↔ java.time.Instant
…keeps everything simple, clear, and tidy. Every step uses UTC. Later in your app you can adjust into time zones as need be.
But what about other date-time strings input/output from Postgres, such as interactive SQL session in pgAdmin?
If you have a date-time string lacking in any offset-from-UTC, then Postgres applies your SQL session’s current default time zone in the process of converting to UTC for internal storage. I consider passing around date-time strings without any offset or time zone to be a bad practice.
If your date-string contains an offset-from-UTC, then Postgres uses that information in adjusting to UTC for internal storage.
The following example shows both behaviors. I call SET TIME ZONE to make explicit the current default time zone in this SQL session. The time zone America/Los_Angles has an offset of -08:00 in January. You can conveniently run this code in your own database as the TEMP in CREATE TEMP TABLE means the table is automatically dropped when that SQL session closes.
Notice how in the second case the hour is 04 versus 12. These two values do not represent the same moment on the timeline; these are two different points in time.
SET TIME ZONE 'America/Los_Angeles';
DROP TABLE IF EXISTS some_table_ ;
CREATE TEMP TABLE IF NOT EXISTS some_table_ (
"some_datetime_" TIMESTAMP WITH TIME ZONE NOT NULL
) ;
INSERT INTO some_table_
VALUES ( '2016-01-23 12:34:01' ); -- Lacking offset-from-UTC. *Not recommended*!
INSERT INTO some_table_
VALUES ( '2016-01-23 12:34:02Z' ); -- 'Z' means Zulu = UTC.
TABLE some_table_ ;
2016-01-23 12:34:01-08
2016-01-23 04:34:02-08
Very Important: Be clear that the behavior above is when your column data type is TIMESTAMP WITH TIME ZONE. The other type, TIMESTAMP WITHOUT TIME ZONE, should almost never be used as it means “make no adjustment, ignore any offset or time zone, just take this date and this time and stuff it blindly into the database”.