Skip to content

Instantly share code, notes, and snippets.

@squito

squito/equality_parsing_psql_timestamp.md

Last active September 11, 2017 16:27
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save squito/cb81a1bb07e8f67e9d27eaef44cc522c to your computer and use it in GitHub Desktop.
Save squito/cb81a1bb07e8f67e9d27eaef44cc522c to your computer and use it in GitHub Desktop.
Download ZIP
example of how postgres treats different timestamp types, both parsing and as the timezone is changed
Raw
equality_parsing_psql_timestamp.md

References

  1. If is not specified, then WITHOUT TIME ZONE is implicit

Parsing Strings

Timestamp & Timestamp with time zone have different behaviors on how they parse input, and how they interact with the session timezone.

psql                                                                                      
psql (9.6.2)
Type "help" for help.
irashid=# create table ts_test(
  no_tz timestamp,
  with_tz timestamp with time zone
);
CREATE TABLE
irashid=# insert into ts_test values ('2017-05-24 12:00:00', '2017-05-24 12:00:00');
INSERT 0 1
irashid=# insert into ts_test values ('2017-05-24 12:00:00+4', '2017-05-24 12:00:00+4');
INSERT 0 1
irashid=# select * from ts_test;
        no_tz        |        with_tz         
---------------------+------------------------
 2017-05-24 12:00:00 | 2017-05-24 12:00:00-05
 2017-05-24 12:00:00 | 2017-05-24 03:00:00-05
(2 rows)

irashid=# show timezone;
  TimeZone  
------------
 US/Central
(1 row)

irashid=#  SET TIME ZONE 'Europe/Berlin'
irashid-# ;
SET
irashid=# select * from ts_test;
        no_tz        |        with_tz         
---------------------+------------------------
 2017-05-24 12:00:00 | 2017-05-24 19:00:00+02
 2017-05-24 12:00:00 | 2017-05-24 10:00:00+02
(2 rows)

EQUALITY

Perhaps most elucidating is the difference in the notion of equality of these types

  • timestamp (aka timestamp without time zone) ignores the timezone when considering equality.
  • timestamp with time zone only care about the instant in time represented. The time zones don't have to be the same for the values to be equal; only the instant in time.
irashid=# create table raw_ts_strings (str varchar);
irashid=# insert into raw_ts_strings values 
('2017-05-24 12:00:00+6'),
('2017-05-24 10:00:00+4'),
('2017-05-24 12:00:00+4');
INSERT 0 3
irashid=# select 
   a.str as a,
   b.str as b, 
   (cast (a.str as timestamp) = cast (b.str as timestamp)) as timestamp_equality,
   (cast (a.str as timestamp with time zone) = cast (b.str as timestamp with time zone)) as timstamp_with_tz_equality
 from raw_ts_strings as a cross join raw_ts_strings as b
 where a.str < b.str -- just to limit duplicates & obvious self-equality.
 ;
           a           |           b           | timestamp_equality | timstamp_with_tz_equality 
-----------------------+-----------------------+--------------------+---------------------------
 2017-05-24 10:00:00+4 | 2017-05-24 12:00:00+6 | f                  | t
 2017-05-24 10:00:00+4 | 2017-05-24 12:00:00+4 | f                  | f
 2017-05-24 12:00:00+4 | 2017-05-24 12:00:00+6 | t                  | f
(3 rows)

Joins and Implicit Casts

-- nothing special about "-5", just what timezone happens to be, putting it here for repeatability
irashid=# SET TIME ZONE -5;
irashid=# insert into w_tz values 
irashid-# ('2017-05-24 10:00:00-5'),
irashid-# ('2017-05-24 10:00:00-6');
INSERT 0 2
irashid=# insert into wout_tz values 
irashid-# ('2017-05-24 10:00:00'),
irashid-# ('2017-05-24 11:00:00');
INSERT 0 2
irashid=# select * from w_tz;
        ts_w_tz         
------------------------
 2017-05-24 10:00:00-05
 2017-05-24 11:00:00-05
(2 rows)

irashid=# select * from wout_tz;
     ts_wout_tz      
---------------------
 2017-05-24 10:00:00
 2017-05-24 11:00:00
(2 rows)

irashid=# select w_tz.ts_w_tz, wout_tz.ts_wout_tz from w_tz join wout_tz on (w_tz.ts_w_tz = wout_tz.ts_wout_tz);
        ts_w_tz         |     ts_wout_tz      
------------------------+---------------------
 2017-05-24 10:00:00-05 | 2017-05-24 10:00:00
 2017-05-24 11:00:00-05 | 2017-05-24 11:00:00
(2 rows)

-- if we change the timezone, the "w_tz" values display differently, so different join:
irashid=# SET TIME ZONE -6;
irashid=# select * from w_tz;
        ts_w_tz         
------------------------
 2017-05-24 09:00:00-06
 2017-05-24 10:00:00-06
(2 rows)
irashid=# select w_tz.ts_w_tz, wout_tz.ts_wout_tz from w_tz join wout_tz on (w_tz.ts_w_tz = wout_tz.ts_wout_tz);
        ts_w_tz         |     ts_wout_tz      
------------------------+---------------------
 2017-05-24 10:00:00-06 | 2017-05-24 10:00:00
(1 row)

DST

TODO include some examples of times that do not exist in session timezone due to DST

Raw
jdbc_timestamp.md

Developers that are used to interacting with SQL via JDBC are also led astray by the representation of timestamp without time zone as java.sql.Timestamp, which is a thin wrapper around a nanos-since-epoch value. This naturally seems more like it represents an instant-in-time, not a display time.

But, thats the wrong way to think about it. java.sql.Timestamp is just an in-memory representation which makes sense for the current JVM timezone.

Here are some examples. After downloading the postgres jdbc driver, you can try this by running

> scala -cp postgresql-42.1.4.jar

then running this code:

import java.sql.{DriverManager, Timestamp}
import java.util.Date
val dbName = "irashid"
val conn = DriverManager.getConnection(s"jdbc:postgresql:$dbName")

val tsValues = Seq(-2,-1,0,1,2).map { tz =>
  val stmt = conn.createStatement()
  val tsStr = s"'1970-01-01 10:00:00${if (tz < 0) tz else s"+$tz"}'"
  val query = s"select cast($tsStr as timestamp without time zone)"
  println(query)
  val rs = stmt.executeQuery(query)
  rs.next()
  val ts = rs.getTimestamp(1)
  ts
}
val millisSet = tsValues.map{_.getTime()}.toSet
println(millisSet)
println(new Date(millisSet.head))
println(new Timestamp(millisSet.head))

you'll see that every query returns the exact same instant -- an instant which ignores the time zone specified in the original query, but formats to the same value in the current JVM timezone.

select cast('1970-01-01 10:00:00-2' as timestamp without time zone)
select cast('1970-01-01 10:00:00-1' as timestamp without time zone)
select cast('1970-01-01 10:00:00+0' as timestamp without time zone)
select cast('1970-01-01 10:00:00+1' as timestamp without time zone)
select cast('1970-01-01 10:00:00+2' as timestamp without time zone)
Set(57600000)
Thu Jan 01 10:00:00 CST 1970
1970-01-01 10:00:00.0

OTOH, lets try to pull out the same value from jdbc, but just change our timezone in the jvm:

import java.sql.DriverManager
import java.util.TimeZone
val dbName = "irashid"
val conn = DriverManager.getConnection(s"jdbc:postgresql:$dbName")

val tsValues = Seq("America/Los_Angeles", "America/New_York").map { tz =>
  TimeZone.setDefault(TimeZone.getTimeZone(tz))
  val stmt = conn.createStatement()
  val tsStr = s"'1970-01-01 10:00:00'"
  val query = s"select cast($tsStr as timestamp without time zone)"
  println(s"JVM TZ is $tz, running $query")
  val rs = stmt.executeQuery(query)
  rs.next()
  val ts = rs.getTimestamp(1)
  println(s"got TS $ts with underlying millis ${ts.getTime()}")
  println()
  ts
}
val millisSet = tsValues.map{_.getTime()}.toSet
println(millisSet)

The nanos-since-epoch in the Timestamps are now distinct -- though they format the same as the query with the current JVM TZ.

JVM TZ is America/Los_Angeles, running select cast('1970-01-01 10:00:00' as timestamp without time zone)
got TS 1970-01-01 10:00:00.0 with underlying millis 64800000

JVM TZ is America/New_York, running select cast('1970-01-01 10:00:00' as timestamp without time zone)
got TS 1970-01-01 10:00:00.0 with underlying millis 54000000

Set(64800000, 54000000)

Note this is not the case for timestamp with time zone -- changing the JVM timezone changes display, but not the instant in time:

import java.sql.DriverManager
import java.util.TimeZone
val dbName = "irashid"
val conn = DriverManager.getConnection(s"jdbc:postgresql:$dbName")

val tsValues = Seq("America/Los_Angeles", "America/New_York").map { tz =>
  TimeZone.setDefault(TimeZone.getTimeZone(tz))
  val stmt = conn.createStatement()
  val tsStr = s"'1970-01-01 10:00:00'"
  val query = s"select cast($tsStr as timestamp with time zone)"
  println(s"JVM TZ is $tz, running $query")
  val rs = stmt.executeQuery(query)
  rs.next()
  val ts = rs.getTimestamp(1)
  println(s"got TS $ts with underlying millis ${ts.getTime()}")
  println()
  ts
}
val millisSet = tsValues.map{_.getTime()}.toSet
println(millisSet)

This time we get:

JVM TZ is America/Los_Angeles, running select cast('1970-01-01 10:00:00' as timestamp with time zone)
got TS 1970-01-01 08:00:00.0 with underlying millis 57600000

JVM TZ is America/New_York, running select cast('1970-01-01 10:00:00' as timestamp with time zone)
got TS 1970-01-01 11:00:00.0 with underlying millis 57600000

Set(57600000)

(The exact strings you get will depend on your system timezone -- my postgres instance defaults to America/Chicago, my current timezone.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment