Working with time in Postgres

http://www.craigkerstiens.com/2017/06/08/working-with-time-in-postgres/

by neovintage • 9 years ago

343 120 9 years ago

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.

craigkerstiens4• 9 years ago

Doh! Will fix.

i_feel_great• 9 years ago

What a ballsup

cuu508• 9 years ago

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.

yawaramin• 9 years ago

I'm a bit of a n00b at date handling. What's the benefit to communicating with TZ-aware timestamps versus with TZ-less timestamps with the shared understanding that they're always at UTC? With the latter approach I can also convert to my local timestamp for display.

zAy0LfpBZLC8mAC5• 9 years ago

That the database can't do computations on what it's not aware of. If you want to ask the database for "events today", the database needs to know what span of time corresponds to "today" for the user.

jontro3• 9 years ago

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');

craigkerstiens4• 9 years ago

Correct, it would give the results from this exact moment in time to that same timestamp 7 days ago. Were you thinking it might give you up to say the start of the last week or something?

jontro3• 9 years ago

Reading the end of the sentence "within the past week:" just above. However I would be interested to know if the "last week" date range is easily doable in postgres :)

fphilipe2• 9 years ago

One week is always 7 days. But one month (and year) is not always the same length. If you add or subtract n months from a timestamp or date at the beginning or the end of the month, it returns the beginning or end of the month n months away. Here's an example showcasing this using the fact that 2016 was a leap year:

    $ 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

deepsun2• 9 years ago

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.

lacampbell3• 9 years ago

Would it be that much work to add a smallint field, that had the original UTC offset used for your time?

manigandham8• 9 years ago

Timezones are more than just an offset.

flukus• 9 years ago

While I agree with the other replies, using a smallint would assume all timezones are offset in hourly increments, which isn't the case.

piinbinary• 9 years ago

You can do that with the 'TIMESTAMP WITH TIME ZONE' column type.

https://www.postgresql.org/docs/current/static/datatype-date...

(Edit: or not. See child comment)

waffle_ss2• 9 years ago

Nope, that doesn't store the time zone, it just uses time zone information before flattening to UTC time.

https://stackoverflow.com/a/9576170/215168

Nullabillity2• 9 years ago

When would you want use something else than UTC for business logic? Time zones (and their related nonsense) should be a view-layer concern.

baddox4• 9 years ago

Perhaps because there's not always a one to one relation between a time with zone and a unix time stamp.

zAy0LfpBZLC8mAC5• 9 years ago

Because there actually are times that are specified in terms of local time and are not fixed to a specific timezone. Take a birthday, for a trivial example: The span of time in UTC that corresponds to someone's birthday depends on their location at the time.

oftenwrong• 9 years ago

For an example of how storing a UTC datetime for a future event can go wrong, see my comment:

https://news.ycombinator.com/item?id=12988092

fnord1232• 9 years ago

That is correct behaviour. Time zone information is a presentation detail.

yen223• 9 years ago

Not entirely. Thanks to daylight-savings, you need time zone information to properly calculate lengths of timespans, e.g. for daily recurrences

revicon2• 9 years ago

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,
          ^

cmaggard• 9 years ago

Remove the comma before the SELECT?

barsonme• 9 years ago

yeah, the comma after the "with" block shouldn't be there.

i.e.,

    ... weeks
    )
    SELECT weeks.week

revicon2• 9 years ago

makes sense. After removing it...

  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

it throws...

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

I would move this to the post's own "replies" section, but it doesn't have one.

rainbowliquor2• 9 years ago

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

extesy• 9 years ago

It's not the same: date_trunc returns timestamp, to_char returns a string.

rainbowliquor2• 9 years ago

True but in the use case of OPs example. In the article OP says "So if we wanted to find the count of users that signed up per week:" which "2017-06-05 00:00:00+00" isn't a week, it's a date (with a time stamp as well which isn't pertinent) which happens to be the beginning of a week. Using TO_CHAR() with a format string makes it more legible and more recognizable.

fphilipe2• 9 years ago

Note that there's the ISO standard for weeks which uses slightly different abbreviations:

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

The difference is when the first week of the year starts. Compare yours to the ISO 8601 format for January 1 this year:

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

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

ender7• 9 years ago

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.

waffle_ss2• 9 years ago

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

akira2501• 9 years ago

There's always TAI64.

http://dyscour.se/post/12679668746/using-tai64-for-logging

klodolph2• 9 years ago

I'm with Google on this one... Just smear the leap seconds out in the general case, and anything that needs to be within 500ms of UTC can be handled as a special case.

gshulegaard• 9 years ago

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

mason55• 9 years ago

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.

baddox4• 9 years ago

Can you provide examples? It only seems useful to me for display purposes if you know you'll never need to worry about any specific local time zone (or if you will, then you know what time zone that is and know how to deal with it).

baddox4• 9 years ago

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

stickfigure3• 9 years ago

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).

chrisan• 9 years ago

If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time.

Does this come up a lot? Moving from Las Vegas to Chicago would involve much more than just being aware of the time zone change.

l0b0• 9 years ago

Unix timestamps are bound to UTC[1], so it would never be correct to have a timestamp represent number of seconds since 1970 _somewhere_. You would have to convert the timezone for any location you want it to be relative to.

[1] https://en.m.wikipedia.org/wiki/Unix_time

klodolph2• 9 years ago

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.

heavenlyblue• 9 years ago

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?

solidsnack90002• 9 years ago

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?

gtrubetskoy• 9 years ago

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.

jontro3• 9 years ago

Probably because it's using ISO-8601 week numbers. https://en.wikipedia.org/wiki/ISO_week_date

lobster_johnson• 9 years ago

Postgres uses the ISO definition of week for "week", which starts on Monday. For "dow", it uses the American week definition.

anarazel• 9 years ago

isodow for the sane definition ;)

elmigranto• 9 years ago

Sunday, which in some/most? countries is the first day of the week.

Just like imperial system, only a couple of weirdos do that.

davidw• 9 years ago

"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...

luhn• 9 years ago

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.

orf3• 9 years ago

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

craigkerstiens4• 9 years ago

Yes! Range types should absolutely be in here. I had them as one of the top items in a recent post so thought it'd be a bit repetitive[1], but in retrospect, it should absolutely be here as well.

[1] http://www.craigkerstiens.com/2017/04/30/why-postgres-five-y...

jldugger• 9 years ago

All you have to is just self-cite and say you won't belabor the point any further. ^_^

neuronexmachina• 9 years ago

I'm so sad range types are unsupported in AWS Redshift. :(

slig• 9 years ago

They're are awesome and Django's ORM has built in support for it.

https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/...

TheCoreh2• 9 years ago

Do you know if they can be combined with the timetravel extension?

orf3• 9 years ago

No idea, maybe? You mean this[1]?

1. https://www.postgresql.org/docs/9.1/static/contrib-spi.html#...

nthcolumn• 9 years ago

I idled here expecting something on time series data. The article whilst useful is incredibly thin, a one page blog post, which is fine but I'm not sure why it has lasted very long here? But thanks for that also, perhaps the thread will beget something more comprehensive. (Not volunteering)

scott_karana• 9 years ago

Practically speaking, how would you use these for noting starts and ends of long-running jobs, say?

Would you set the interval starting time, but leave the end of the interval as "present"/infinity? And then update the end of the interval when the job finished? Wouldn't you also need to have a cleanup function to manually "close" intervals if the worker crashed and restarted?

mixmastamyk2• 9 years ago

Maybe a chosen value such as "2100-01-01 00:00" could work.

orf3• 9 years ago

I'm not sure to be honest, I would set the end as infinity I think.

I wouldn't have the worker process handle this itself though, as you would need some form of cleanup. But you'd need the same with two individual columns

CharlesW• 9 years ago

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

andrewfromx• 9 years ago

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

kclay• 9 years ago

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.

mirekrusin• 9 years ago

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.

midmoon2001• 9 years ago

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

greggyb2• 9 years ago

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.

solidsnack90002• 9 years ago

Do you guys use a custom date type, then?

greggyb2• 9 years ago

No. Dates are dates. Everyone can agree when a specific day exists. It's all about grouping.

Dates have attributes that group them together. "Month" is an attribute that you're familiar with. "Fiscal Period" can take on many specific definitions but it is analogous to "Month".

Those two concepts share a lot of properties. They each collect a series of contiguous dates. Each is adjacent to a similar grouping that falls sequentially and the last date that exists in one is one day prior to the first day that exists in the next. Each falls within a larger category like "Year" or "Fiscal Year".

Year+Period forms a composite key for a period. We can also assign a monotonically increasing field that increments by one with each subsequent period. That field allows simple arithmetic to shift forward and backward. We typically call this attribute PeriodIndex or PeriodSequential. I'll abbreviate to PI here.

If you have a reference PI, you can always find the immediate prior period by subtracting one from the reference. We can assign these for any grain of time period. We typically see Week, Period, Quarter, Semester, Year.

This is the baseline of how we handle dates. There are plenty of utility fields we'll maintain for specific time-based needs, but it's all sugar on top of that.

mrtbld2• 9 years ago

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.

mixmastamyk2• 9 years ago

Thanks. If I store all datetimes from my app in UTC, with end users in more than one timezone, which type should I use?

mrtbld2• 9 years ago

Well I would use timestamptz, using user's timezone only to convert for display. Use cases for timestamp are very limited.

Just make sure you include a timezone info in string representations in your SQL queries. For example '2000-01-01T00:00:00Z' where Z stands for UTC. Otherwise that would insert a timestamp into a timestamptz column, in which case postgres uses local timezone setting for conversion, implicitly; this is not what you want.

See http://phili.pe/posts/timestamps-and-time-zones-in-postgresq...

Also you should use an equivalent type in you app, i.e. python datetime with tzinfo or JS Date. And beware of UTC offsets: they can't handle DST. Python pytz and JS moment-timezone provide DST-aware timezone info (which is built-in in postgres).

Edit: if you can rely on your users system time for display that's even better because you wouldn't have to explicitly deal with those DST-aware timezone info.

joeclark77• 9 years ago

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.

eastern• 9 years ago

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.

Dowwie• 9 years ago

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...

pvaldes• 9 years ago

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.

Working with time in Postgres

Related Stories