Make a Date with Postgres temporal data types , count-from-epoch, offset-from-UTC, time zone, “local” vs zoned, UTC vs GMT, timestamp w/ & w/o time zone data types, java.time Basil Bourque basil.bourque@pobox.com LinkedIn: basilbourque 2018-01-31 Key concepts for date-time handling 1

“Fly Like an Eagle” The Steve Miller Band Time keeps on 
 slipping, slipping, slipping… Into the future 2

caveat • Frame-of-reference: 
 practical, real-world, business-oriented apps • Not: 
 science, engineering, astronomy, history, non-Western calendars • Not everything, just core concepts of time-keeping
 Goal: Deep, clear understanding of data types for date-time work. 3

timeline Now future past 4

epoch reference date Now future past Epoch Count - + 5

epochs January 0, 1 BC January 1, 1 AD January 1, 1601 December 31, 1840 November 17, 1858 December 30, 1899 December 31, 1899 January 0, 1900 January 1, 1900 January 1, 1904 December 31, 1967 January 1, 1970 January 1, 1980 January 6, 1980 January 1, 2000 January 1, 2001 6

Unix epoch = 1970-01-01 00:00:00 UTC January 0, 1 BC January 1, 1 AD January 1, 1601 December 31, 1840 November 17, 1858 December 30, 1899 December 31, 1899 January 0, 1900 January 1, 1900 January 1, 1904 December 31, 1967 January 1, 1970 January 1, 1980 January 6, 1980 January 1, 2000 January 1, 2001 7

count of what? Posix time ( Unix ) second 0 legacy Java classes millisecond 0.123 Postgres 9 microsecond 0.123456 nanosecond 0.123456789 java.time 8

nanosecond Admiral Grace Hopper giving David Letterman a nanosecond 9

count from where ? • “noon” = Sun overhead = ‘Solar Time’ • Zone = Standardized over a wider swath of land • Zones are crucial
 Minutes after midnight Paris FR = still “yesterday” in Montréal Québec • From one of these? GMT PDT EST Z IST UTC CST 10

UTC • Never use 3-4 letter pseudo-zones • Not real time zones, not standardized, not unique (!) • Real time zones = continent/region • America/Los_Angeles Pacific/Auckland Asia/Kolkata Africa/Casablanca • Wikipedia, “List of tz database time zones” GMT PDT EST Z IST UTC CST 11 Icon by: http://iconka.com/

UTC, Z, GMT • UTC ≈ GMT (in practice) • Leap Second, every couple years
 23:59:60 or 23:59:61
 Approx. half-minute delta now • Irrelevant • Z = Zulu = UTC • Learn to think in UTC & 24-hour clock • UTC = The One True Time™ 12

offset-from-UTC 13

offset-from-UTC • Number of hours & minutes & seconds • Seattle: -08:00 | Québec: -05:00 | India: +05:30 • Current maximum: -12:00 +14:00 • Tip: Stick with padded zero: +05:30 not +5:30
 Use: ±hh:mm Not: ±hhmm ±hh ±h 14 • Not a formula in algebra: 2016-01-23T04:34:56.789-08:00 • + is ahead of UTC
 - is behind UTC • Flip the sign to calculate UTC value

zone • Named continent/region ex. Europe/Paris Asia/Kolkata Pacific/Auckland
 - Never use 3-4 letter codes • Time Zone = collection of Offset-from-UTC values • A zone is a *history* of past, present, and future offsets • Always use zone, when known(!), in preference to a mere offset • Offset does not imply Zone. Zones share offsets. • See Wikipedia for List of tz database time zones 15

Strings • Ambiguity: 1/2/3 • Do not conflate textual representation with date-time object/value • Use objects to exchange date-time values with database, not strings • Beware of time zone being applied not-so-helpfully to UTC values • Postgres default in console session. Ditto legacy Java classes. 16 • Do not focus on formats. • Like i18n/l10n, separate data model from presentation. • ISO 8601 – practical, sensible, unambiguous formats. • Like SQL, with a `T`. 2017-01-23T12:34:56.789Z

Joda-Time IT industry ignores date-time problem 17 ?… … `Date` `Calendar` … Noda Time ( .Net ) java.time … SQL standard ThreeTen- Backport ThreeTenABP

java.time – core classes • Instant – moment on timeline, in UTC, nanosecond resolution • OffsetDateTime – Adjusted from UTC by a number of hours & minutes & seconds • ZonedDateTime – Adjusted into a time zone • Time zone = History of offsets used in the past, present, & future by people in a particular region. • Offset changes because of anomalies. 18 Instant OffsetDateTime ZonedDateTime on timeline

Anomalies • Historical adjustments ( war, occupation, modern time-keeping, etc. ) • Political statements ( 15 minutes, etc. ) • Daylight Saving Time ( DST ) – 2 per year, or staying on or staying off. • Frequent changes by politicians, often with little forewarning • “tzdata” – formerly “Olson database” ( volunteer ) – now IANA • Surprisingly, not well-attended by authorities. Be wary < 1970. 19

Adjusting • Instant instant = Instant.parse( “2017-01-23T01:23:45Z” ) ;
 instant.toString(): 2017-01-23T01:23:45Z • OffsetDateTime odt = instant.atOffset( ZoneOffset.of( 5 , 30 ) ) ;
 odt.toString(): 2017-01-23T06:53:45+05:30 • ZonedDateTime zdt = instant.atZone( ZoneId.of( “America/Montreal” ) ) ;
 zdt.toString(): 2017-01-22T20:53:45-05:00 • All 3 are same moment, different wall-clock time 20

‘local’ = un-zoned 21 LocalDateTime LocalTime LocalDate off timeline Instant OffsetDateTime ZonedDateTime on timeline UTC ±00:00:00 continent/region

“Local…” • Imprecise, no real meaning - Not a point on the timeline • Possible moments, becomes real when placed in context of a time zone • All AcmeCorp factories take lunch Noon to 1 PM 22 Epoch Past Future Düsseldorf Dehli Detroit tomorrow noon now

Santa 23 Kiribati 2017-12-25T00:00:00 Pacific/Kiritimati

standard SQL types 24 “LOCAL” ZONED DATE-ONLY DATE TIME-ONLY TIME TIME WITHOUT TIME ZONE TIME WITH TIME ZONE DATE-TIME TIMESTAMP TIMESTAMP WITHOUT TIME ZONE TIMESTAMP WITH TIME ZONE SQL STD

Postgres types 25 “LOCAL” ( IGNORE OFFSET/ZONE ) UTC ( CONVERT TO UTC USING OFFSET/ZONE ) DATE-ONLY DATE TIME-ONLY TIME TIME WITHOUT TIME ZONE TIME WITH TIME ZONE DATE-TIME TIMESTAMP TIMESTAMP WITHOUT TIME ZONE TIMESTAMPZ TIMESTAMP WITH TIME ZONE ⾠

Java vs Postgres 26 LocalDateTime LocalTime LocalDate off timeline ‘local’ ≠ zoned Instant OffsetDateTime ZonedDateTime on timeline ⃠ DATE TIME W/O ZONE TS W/O ZONE TS WITH ZONE

offset/zone going into Postgres 27 2017-01-23T18:00:00+05:30[Asia/Kolkata] 2017-01-23 18:00:00 TIMESTAMP WITHOUT TIME ZONE TIMESTAMP WITH TIME ZONE 2017-01-23 12:30:00 UTC without respect for… with respect for…

pop quiz • Birthdate • Lunch appointment tomorrow • Daily pasturing of the cows • When invoice received • Annual physical exam appt • Logging current moment • Employee hired 28 • Next dental appointment • Opening/closing of election polls in US • Scheduling slots for talks at next year’s conference • Weekly appt ( pool , housecleaner ) • Closing on purchase of a home

SQL standard types • Time precision • Default to 0 • Maximum >= 6 ( microseconds ) • Beware: Casting from “WITHOUT TIME ZONE” converts to UTC 29

SQL standard keywords • CURRENT_DATE
 CURRENT_TIME
 CURRENT_TIMESTAMP • CURRENT_TIME( p )
 CURRENT_TIMESTAMP( p ) • LOCALTIME
 LOCALTIMESTAMP • LOCAL_TIME( p )
 LOCAL_TIMESTAMP( p ) 30 • TIME
 TIMESTAMP
 TIMEZONE_HOUR
 TIMEZONE_MINUTE • YEAR
 DATE
 DAY • HOUR
 MINUTE
 SECOND • LOCAL no parens

When is now ? • Standard functions all based on transaction start time • transaction_timestamp() – A more clear name
 now() – synonym • statement_timestamp() – “time of receipt of the latest 
 command message from the client” • clock_timestamp() – current moment, 
 can change within a single SQL command • ignore: timeofday() – historical, returns string in bad format, use ‘clock_timestamp()’ 31 TXN STMT CLOCK TXN parens

SQL session • Default time zone ☹ 32 SELECT current_timestamp > TIMESTAMP WITH TIME ZONE 2017-07-02 17:27:04.037628 DataGrip/IntelliJ 2017-07-02 17:27:04.037628-07 pgAdmin4 SELECT localtimestamp > TIMESTAMP WITHOUT TIME ZONE 2017-07-02 17:27:04.037628 DataGrip/IntelliJ 2017-07-02 17:27:04.037628 pgAdmin4 SELECT timezone | SELECT time zone | SELECT current_setting( 'timezone' ) ; > America/Los_Angeles DataGrip/IntelliJ > US/Pacific pgAdmin4

set time zone • Session setting (temporary) • SET timezone TO 'UTC' ; -- Setting config parameter • SET TIME ZONE 'UTC' ; -- Standard SQL • Takes offset as INTERVAL, or LOCAL • Example: INTERVAL +'14:00' • Permanent: • Set "timezone" line in your postgresql.conf file 33

UTC 34 SELECT current_timestamp > TIMESTAMP WITH TIME ZONE 2017-07-03 02:24:36.895098 +00:00 DataGrip/IntelliJ 2017-07-03 02:24:36.895098+00 pgAdmin4 SELECT localtimestamp > TIMESTAMP WITHOUT TIME ZONE 2017-07-03 02:24:36.895098 DataGrip/IntelliJ 2017-07-03 02:24:36.895098 pgAdmin4 2017-07-02 19:24:36.895098 -07:00 [America/Los_Angeles]

zone strategy • For command-line sessions, set time zone to UTC • For database drivers: • Should be getting moment in UTC or a “Local…”
 ( Java/JDBC → Instant object or LocalDateTime object ) • Read the doc, study thoroughly • Practice, practice, practice 35

take-aways • Date-time work is tricky stuff.
 - Lame support across IT biz. • Forget parochial time • Think, work, log in UTC • When in doubt, use UTC • “Local…” does not mean local • Consider “local” time for future • Zones > Offsets 36 • Fractional second matters (truncate?) • Strings != Date-times • Timestamp With Time Zone uses then discards time zone (actually UTC)
 - …in Postgres. Varies by db. • Ignore the nerds suggesting the use of count-from-epoch • Invite me back for Make Another Date With Postgres

« fin » 37 Basil Bourque basil.bourque@pobox.com LinkedIn: basilbourque