Make a date with Postgres

This talk is a practical guide to the core concepts for handling date-time values with a Postgres database.

Working with date-time values is surprisingly tricky and complicated. Our intuitive understanding of the clock and calendar actually works against us as database admins and developers. Issues like time zones, offsets, UTC/GMT, Daylight Saving Time (DST), epoch, Leap Year, Leap Second, and historical anomalies create confusing complications. Add to that the very poor support for date-time handling in most databases and programming languages, and we have a troublesome mess.

Let’s walk through the fundamental concepts behind tracking time. First we cover how computers track time, with the epoch reference date, a count-from-epoch, and the resolution/granularity of that count. Then we move on to the exact meaning of UTC/GMT, offsets from UTC, and come to understand time zone as a history of those offsets.

With this understanding of zoned time in place, we move on to un-zoned “local” time. Through practical examples of scheduling and logistics planning, we learn when to use zoned time and when to use un-zoned time.

We explore these zoned and un-zoned notions of time first by looking at the core classes of the java.time framework newly added to Java. Some simple one-line programming examples exercise all the concepts discussed above.

We move on to map those concepts to the standard SQL data types for date-time, and to the data types found in Postgres. We practice these concepts with a series of business cases such as dental appointments, milking cows, and closing on the purchase of a home.

Along the way, some practical tips challenge you to shift your thinking to avoid confusion during date-time work. For example, we learn to not conflate date-time values with strings representing those values.

This guided tour of Postgres data types for date-time will leave you ready and certain when choosing the right type for your data. Replace your hope-and-prayer approach to date-time handling with a new confidence built on clear understanding. Handling date-time will be much easier, more predictable, and even enjoyable.

Updated 2018-01.

By Basil Bourque
LinkedIn: basilbourque

Transcript

1. 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
2. “Fly Like an Eagle” The Steve Miller Band Time keeps on 
 slipping, slipping, slipping… Into the future 2
3. 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
4. timeline Now future past 4
5. epoch reference date Now future past Epoch Count - + 5
6. 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
7. 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
8. 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
9. nanosecond Admiral Grace Hopper giving David Letterman a nanosecond 9
10. 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
11. 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/
12. 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
13. offset-from-UTC 13
14. 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
15. 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
16. 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
17. Joda-Time IT industry ignores date-time problem 17 ?… … `Date` `Calendar` … Noda Time ( .Net ) java.time … SQL standard ThreeTen- Backport ThreeTenABP
18. 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
19. 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
20. 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
21. ‘local’ = un-zoned 21 LocalDateTime LocalTime LocalDate off timeline Instant OffsetDateTime ZonedDateTime on timeline UTC ±00:00:00 continent/region
22. “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
23. Santa 23 Kiribati 2017-12-25T00:00:00 Pacific/Kiritimati
24. 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
25. 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 ⾠
26. 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
27. 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…
28. 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
29. SQL standard types • Time precision • Default to 0 • Maximum >= 6 ( microseconds ) • Beware: Casting from “WITHOUT TIME ZONE” converts to UTC 29
30. 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
31. 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
32. 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
33. 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
34. 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]
35. 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
36. 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
37. « fin » 37 Basil Bourque basil.bourque@pobox.com LinkedIn: basilbourque