You cannot have a post entitled "Working with time in Postgres" and fail to mention Range Types[1]!

If you're using Postgres right now and have any columns like start_* and end_* for anything (e.g numbers or dates), you need to stop what you are doing and use a range type. They are amazing. You can do things like a unique index that ensures there are no overlapping ranges, you can do efficient inclusion/exclusion indexing and much more.

Use them. I'm always surprised more people don't know about them.

1. https://www.postgresql.org/docs/9.6/static/rangetypes.html

"I was in favour of space exploration until I realised what it'd mean for date time libraries" -- Joe Wright

https://twitter.com/joe_jag/status/510048646482894848?lang=e...

> Postgres has two types of timestamps. It has a generic timestamp and one with timezone embedded in it.

That's not correct, timestamptz doesn't have a timezone embedded in it. It's just that it's timezone-aware. A timestamptz corresponds to a universal point in time that have many human reprensentations, one for each timezone. psql uses the default timezone of the postgres instance to convert a timestamptz to a displayable string, so timestamptz are always displayed with a timezone, but that info does not come from the stored value.

Timestamptz needs timezone information only for operations that would give different results in different timezones, e.g. display as string, extract the day part, add a 1-month interval (DST info needed), etc. Comparing two timestamptz however doesn't require any timezone info.

The difference between timestamp and timestamptz is not about what they store, but about how they behave.

Edit: In my experience, this is not always obvious because postgres uses the default timezone of the instance whenever it needs such info with timestamptz operations. Using an explicit timezone often requires convoluted code.

Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can't be mapped unambiguously to a single unix millis timestamp. Usually means a datetime that defaults to the current timezone). Does anyone have a preferred method that avoids these pitfalls?

At the end of the day I always come back to "solution with lots of possible bugs" or "unix millis everywhere". And I always choose the latter. It means we can't use nice date features in a lot of databases, but...eh? They've never seemed worth it.

I love Postgres' time handling, even more so whenever I have to handcraft time-based queries in other databases, like MongoDB (which is more often than I'd like).

Some things the author didn't mention that I like:

* Timestamp with time zone string parsing: '2013-06-27 13:15:00 US/Pacific'::timestamptz

* Timezone-aware to timezone-naive conversion (or vice versa): mytztime AT TIME ZONE 'US/Pacific'

* I haven't used tstzrange yet, but it looks pretty powerful.

Another tip, if you work with per-user custom timezones, then "SELECT some_date AT TIME ZONE %(users_timezone)s" is also sometimes useful and needed.

Normally you would want to receive timezone-aware timestamps from the database, and format them in user's timezone at display time--perhaps in a template. But, if you're e.g. aggregating data for a day-over-day or month-over-month report, then the conversion to naive dates need to happen on the database side, so that day boundaries and month boundaries would match the user's timezone.

I never see discussion of fiscal calendars in these sorts of threads and articles. Whenever I read these, all I see is something that would fall apart as soon as approximately half of my clients look at it.

We have found that it is more annoying to keep track of the behavior of the library and of a home-grown date dimension. In my organization, we tend to use a standardized pattern that can handle arbitrary calendars, even when we're dealing with standard calendars.

One thing I learned about working with postgres and time is that the timezone is based on the timezone of the connecting sever and not actual sever. I can't tell you how long it took me to debug code due to my workstation being at cst but severs in est and then storing dates as utc. Bundle that with caculating upcoming birthdays within 15-30 days before and leap years.

Yeah I didn't like it one bit. Sorta reminds me when I had to develop a Grantt chart component in flex for a client, so many problems with dates.

Fantastic article as usual. One correction: the literal for UTC 00:00:00 00:00:00 is 'allballs', not 'allsballs' as mentioned. I know this because it made me giggle when I first discovered it, and it subsequently became an immature joke around the office for a day or two afterwards.

Last time I checked, I couldn't store a datetime _with_ timezone. It was really strange that such a powerful database doesn't support storing full-ISO datetimes, like '2017-01-01T00:00:00Z'. Instead, it converted it to date-time-only instant, losing information of original timezone along the way. Sure, I could fetch it back using any timezone I want, but I really wanted to know the original timezone it was in.

The week example is a tad misleading, 2017-01-01 is a Sunday, which in some/most? countries is the first day of the week.

If the date were 2016-01-01 and you compared it with what week Postgres thinks it is, you'd get:

  SELECT date_part('week', '2016-01-01'::date);
   date_part 
  -----------
          53
  (1 row)

This is because 2016-01-01 is still the 53rd week of 2015.

Edit: Actually, 2017-01-01 is week 52 according to Postgres, probably because it uses Monday as the first day of the week.

This would have been real useful to me about a week ago as I was writing several of these types of queries!

On the debate of "timestamp vs timestamptz" I reached the opposite conclusion of the author: I've got Amazon RDS instances set to UTC and my timestamps are stored as UTC times with no timezone awareness. Instead, I add the timezone while querying. I think this is better because I never have to remember anything about server settings!

I discovered that the `AT TIME ZONE` clause has two meanings, so I sometimes have to use it twice. In this example which selects all records created this month:

    ...WHERE create_date  AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' > date_trunc('month',current_date)

the first occurrence of `AT TIME ZONE` tells postgres that the timestamp is in UTC (which it is) and the second occurrence subtracts four or five hours (depending on daylight savings time) to show New York time. If I only had the second such clause it would subtract that many hours... it would think I was giving it a New York timestamp and I wanted to see the UTC time.

Actually using generate_series makes little sense. Why should one repeatedly calculate data that will never change.

I have this table:

CREATE TABLE all_dates ( date_stamp date NOT NULL, is_month_end boolean, is_year_end boolean, is_week_end boolean, is_quarter_end boolean, CONSTRAINT all_dates_pkey PRIMARY KEY (date_stamp) )

filled with data from 1st Jan 1980 to 31st Dec 2050, which is the range my application needs.

It's a mere 22k rows and has a whole host of uses.

timestamptz doesn't embed timezone, it stores it as utc without any timezone information.

timestamp does the same - stores value without timezone information.

the difference is with writing/reading those values where timestamptz behaves as you'd expect and timestamp ignores timezone information.

timestamptz - gives you the thing that exists: unique point in time, ie. if person A in australia and person B in europe hits the red button at the same time - timestamptz will have the same value, regardless of the fact that those two timestamp strings had different representations.

timestamp - gives you this local view of time: when person A in australia wakes up 6am to work and person B in europe wakes up at 6am to work - they will hit the snooze button and it will create same value in the database - even though those events happened hours apart.

in both cases you'd have to store timezone in separate column if you want to extract information on which timezone the timestamp was generated in. let me repeat - both cases loose information on timezone. they just do it in different way - timestamp by ignoring it completely and timestamptz by mapping it correctly to unix epoch.

Weird, the example in the post (after changing table/field names for my database)

  with weeks as (
    select week as week
    from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks
  ),

  SELECT weeks.week,
    count(*)
  FROM weeks,
    test_results
  WHERE
    test_results.date_created > weeks.week
  AND
    test_results.date_created <= weeks.week - '1 week'::interval

Throws an error for me...

  ERROR:  syntax error at or near "SELECT"
  LINE 5: SELECT weeks.week,
          ^

    ... weeks
    )
    SELECT weeks.week

  with weeks as (
    select week as week
    from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks
  )

  SELECT weeks.week,
    count(*)
  FROM weeks,
    test_results
  WHERE
    test_results.date_created > weeks.week
  AND
    test_results.date_created <= weeks.week - '1 week'::interval

  ERROR:  column "week" does not exist
  LINE 2:     select week as week
                     ^

I understand by the discussion that if you want to have a field with only four qualitative categories (0, 1, 2, 3 with zero meaning "none" and 3 being "very much") you could use a numrange or int4range for example instead the standard integer type. Interesting. Apart of being much more restrictive in the allowed input, are other advantages (less memory?) or cons (possible portability problems?) that we should be aware of?

Footnote:

> Here’s just a few examples of things you could do with interals:

The author of the article could want to fix the 'interals' typo in the text.

Handling with birthdays and Ages, my Fav: select age('1971-01-01'::date); select age('2015-01-01'::date, '1971-01-01'::date );

date_trunc() is one way but to_char is even better as you can get the resulting output to something nicer. Doing:

  SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP);

gives:

  2017-06-05 00:00:00+00

vs:

  SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-WW"wk"');

gives:

  2017-23wk

    SELECT to_char(now(), 'IYYY-"W"IW');

    $ SELECT to_char('2017-01-01'::date, 'IYYY-"W"IW');
    > 2016-W52

    $ SELECT to_char('2017-01-01'::date, 'YYYY-"W"WW');
    > 2017-W01

Have to mention https://github.com/activityclub/pointspaced in a hn post about time. I use psd for so many queries now vs sql.

Small question / nitpick,

WHERE created_at >= now() - '1 week'::interval

would mean in the last 7 days right? not last week?

Did some work on this recently in mysql and had to resort to calculating this using strtotime('last week');

    $ select '2016-03-31'::timestamp - '1 month'::interval;
    > 2016-02-29 00:00:00

    $ select '2016-03-31'::timestamp + '11 month'::interval;
    > 2017-02-28 00:00:00

    $ select '2016-02-29'::timestamp + '1 year'::interval;
    > 2017-02-28 00:00:00

the generate_series example is a mess

For those who want to implement this in Python, I've written a gist: https://gist.github.com/Dowwie/bec0a29bcd37eea41cde8d5188626...

Any recommendations for similar "best practices" articles/guides for MySQL?

I would say: Think carefully about what kind of 'time' you are trying to represent. Instants like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate.

For example, I wrote an event registration marketplace some time ago. You might think "start time for event" would naturally fit as a unix timestamp, but it's a mistake. If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time. And never store "all day" dates as a timestamp (ie datemidnight); timezone issues can easily produce off-by-one dates.

Basically, 'time' is not a single thing. You usually want to represent it the way your users think about it - and that isn't always like a unix timestamp (although it very often is).

In some sense, Postgres agrees with you, since the underlying storage for a timestamp is something morally equivalent to that. (Milliseconds from 4713 BC.)

However, you do need to do date arithmetic from time to time, whether using a wrapped epoch time in the database or in the application. "One day from now" turns out to be complicated enough that we delegate to libraries to get it right; and Postgres's implementation of these features is solid. When you want to `GROUP BY` day, for example, there are performance benefits to doing that on the database side -- and for analysts, there is often little alternative but to handle dates with DB provided functionality.

When it comes to date arithmetic, how do you handle that with UNIX timestamps?

The problem with Unix milliseconds is it's actually not always increasing thanks to leap seconds. A positive leap second will result in the fractional part of the last second of the day going up to .999..., then resetting to .000... over again.

https://en.wikipedia.org/wiki/Unix_time#Leap_seconds

Standard python date library does not allow to do any sorts of operations between TZ-aware and TZ-unaware dates. You're expected to explicitly convert between the two.

Django postgresql adapter will aggressively show warnings for all of the cases where you're trying to insert a TZ-unaware date into a TZ-aware column.

Is this that hard to reproduce?

It sounds like what you really want is a wrapped "instant" class, which contains something more or less equivalent to a Unix timestamp inside it, but prevents you from accidentally interpreting it using the wrong epoch or units. That's what a Postgres timestamp is. Internally, it's a modified Julian date + nanoseconds since midnight UTC, or something like that. But you don't care if you don't work inside the Postgres source.

It is regrettable that APIs make it easy to create datetime objects without timezones, which is why sane people have moved to e.g. Joda Time and the libraries based on it.

When you talk about Date objects, do you mean datetime? Because a Date with no time compenent has tons and tons of real world uses that a unix time stamp would be inappropriate for.

I have had good success working with Postgres timestamp without time zone.

I don't have much of a preference for milliseconds or seconds, but yeah, Unix time stamps are nearly always the way to go.