java - Timestamps, Timezones, Locales, and Servers -

my head starting spin timestamp , timezone issues. in app, timestamps saved utc. timezones of submitted saved separately.

the environment follows:

• os: centos on san francisco digital ocean droplet
• app server: apache tomcat
• backend: java -> hibernate -> spring
• db: postgresql

the first issue solved fact postgresql default timezone set america/eastern on server in san francisco. switched utc, part of problem , i'm not 100% sure did anything. fyi, column in postgresql timestamp time zone.

the localized date tests, i.e. mst or utc-07:

• "2016-01-20 13:45:19.894 mst"

the utc date used in insert tests:

• "2016-01-20 20:45:19.894z"

first test:----------------------------------------------

with quick sql insert, right date go in. when query, localized date because local system offsets -7 hours mst.

the date after query:

• "2016-01-20 13:45:19.894"

this indeed time inserted locally.

second test:----------------------------------------------

here gets weird. input date through app using same utc timestamp in sql insert.

now comes out when query:

• "2016-01-20 18:45:19.0"

if inserted raw sql, date subtracted 7 hrs local time. if local machine subtracting 7 original utc timestamp, original utc timestamp comes app plus 7 hrs giving us:

• "2016-01-21 01:45:19.0"

instead of put in. huh . . . ?

postgresql seems have made off suspect list because of successful sql inserts , timezone change utc. means culprit might 1 of following:

• java
• tomcat
• centos

my plan methodically change , on server default utc timezone. have idea going on here? plan default utc idea?

let's pull apart piece piece.

i not sure of problem suspect confused misleading behavior of postgres’ presentation of date-time values in console session, described below.

postgres stores in utc

in postgres should using timestamp time zone, virtually never timestamp without time zone. study postgres doc carefully. with type, postgres uses time zone or offset-from-utc provided incoming data adjust given date-time utc. postgres always stores timestamp time zone in utc. passed time zone or offset info forgotten after being used make adjustment utc. behavior may differ other database systems; far know sql spec not specify such details.

when querying via sql console, text generated represent date-time value in timestamp time zone have sql session’s current time zone applied part of text generation. can misleading not mean stored data in time zone. in actuality, timestamp time zone stored in utc, always. experiment in console see yourself, shown below. keep in mind outputs see below represent virtually same moment (i ran 4 in quick succession), wall-clock time quite different.

first use default time zone established interactive sql session. me on development workstation america/los_angeles (west coast of united states), offset-from-utc of -08:00.

select now();  -- or query `timestamp time zone` column. 

2016-01-27 12:16:35.681057-08

try time zone, 1 towards western canada, in alberta, named america/edmonton, offset of -07:00 in standard time , -06:00 in dst.

set time zone 'america/edmonton'; -- offset utc: -07:00 select now();  -- or query `timestamp time zone` column. 

2016-01-27 13:17:01.502281-07

try time zone ahead of utc rather behind it. offset + rather -. notice india 5 and half hours ahead. not time zones have full-hour increments in offset. in example minute-of-hour 47 rather 17 seen above.

set time zone 'asia/kolkata';  -- offset utc: +05:30 select now();  -- or query `timestamp time zone` column. 

2016-01-28 01:47:28.95779+05:30

lastly, try utc. means offset of +00:00, other time zones defined against utc. see z (short zulu) means utc.

set time zone 'utc'; select now();  -- or query `timestamp time zone` column. 

2016-01-27 20:17:50.86757+00

jdbc

in jdbc, should calling gettimestamp on resultset java.sql.timestamp. a java.sql.timestamp always in utc.

annoyingly, , confusingly, tostring method silently applies jvm’s current default time zone when generating string textual representation of date-time value. may seem has time zone while in fact not. these old date-time classes well-intentioned made unfortunate design choices such behavior. reason convert java.time types asap.

java.time

from java.sql types should convert java.time types built java 8 , later. proceed business logic.

the basic java.time types are… instant moment on timeline in utc. instant calling toinstant on java.sql.timestamp. of work should in utc, work instant. apply time zone (zoneid) zoneddatetime when need present user in expected/desired time zone.

specify time zone explicitly

in java.time operations, specify desired/expected time zone. if omitted, jvm’s current default time zone silently applied. why “current”? because jvm’s default time zone can change @ moment, during runtime(!). not rely implicitly on default, explicit. ditto locale, discussion.

avoid old date-time classes

avoid old java.util.date/.calendar classes bundled versions of java. notoriously troublesome. have been supplanted java.time classes.

every piece utc

notice going timestamp time zone in postgres through jdbc java.sql.timestamp java.time instant means in utc. time zone of database server hardware/os not matter. time zone of sql session not matter. time zone on java virtual machine’s host server hardware/os not matter. current default time zone in jvm not matter.

verify clock set correctly

what matter verify hardware clock on database server , on jvm host both correct, set correct time appropriate assigned time zone. while best practice set your servers’ os (and postgres , server jvm) utc, given scenario described above not required.

no, jdbc driver not apply zone

i believe postgresql jdbc driver trying offset utc timestamp based on jvm asks server/os it's default.

no, belief not correct. postgres-provided jdbc driver not apply jvm’s current default time zone java.sql.timestamp. such application irrelevant when going utc in stored database date-time value java.sql.timestamp value in utc.

use proper time zone name

use proper time zone names. come in format of continent/region. never use 3-4 letter codes mst neither standardized nor unique. if meant mountain standard time, use america/edmonton or america/denver.

interactive sql session

using jdbc postgres simplifies date-time handling, mind. moving , from:

timestamp time zone ↔ java.sql.timestamp ↔ java.time.instant

…keeps simple, clear, , tidy. every step uses utc. later in app can adjust time zones need be.

but other date-time strings input/output postgres, such interactive sql session in pgadmin?

if have date-time string lacking in offset-from-utc, postgres applies sql session’s current default time zone in process of converting utc internal storage. consider passing around date-time strings without offset or time zone bad practice.

if date-string contains offset-from-utc, postgres uses information in adjusting utc internal storage.

the following example shows both behaviors. call set time zone make explicit current default time zone in sql session. time zone america/los_angles has offset of -08:00 in january. can conveniently run code in own database temp in create temp table means table automatically dropped when sql session closes.

notice how in second case hour 04 versus 12. these 2 values not represent same moment on timeline; these 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 time zone not null ) ;  insert some_table_ values ( '2016-01-23 12:34:01' ); -- lacking offset-from-utc. *not recommended*!  insert 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: clear behavior above when column data type timestamp time zone. other type, timestamp without time zone, should never used means “make no adjustment, ignore offset or time zone, take date , time , stuff blindly database”.