Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Working with time in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

71–80 of 128 posts

Re: Working with time in Postgres

#71

Post by zAy0LfpBZLC8mAC »

baddox wrote: post #65
mason55 wrote: post #56

Earlier quoted context omitted.

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.

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

Just general calendar arithmetic. There are tons of cases where you need to do stuff like calculate "one month before", "one month after", "beginning of the year", ... --that's a mess with unix timestamps, but trivial with a good calendar library (or postgres, for that matter).

Re: Working with time in Postgres

#72

Post by openasocket »

manigandham wrote: post #57
openasocket wrote: post #49

Earlier quoted context omitted.

That doesn't sound right. If you have a local timestamp with the date and the time and convert it to UTC, shouldn't the datetime library look up the rules for this timezone and convert it correctly? Isn't that the whole point of having timezones be more than offsets?

What doesn't sound right? You might be saying the same thing. To store this appointment correctly, you store the timezone of the appointment (let's say EST) and the timestamp within that timezone (4pm). So the final version is 16:00 EST. You can store a UTC version as well, but you need the local timestamp and timezone first. If you only store the timezone with a UTC timestamp, when you convert back after DST shifts…

No, I mean you make the appointment for December 3rd at 10:00am in Eastern Time (ET), and then you convert it to UTC to put into the database. Your local->UTC conversion library looks at the date and notices that this occurs during Daylight Savings Time for ET, and uses the correct offset when converting. Unless the rules about DST change between now and that meeting this is completely correct, you don't need to store the local timestamp at all.

Re: Working with time in Postgres

#73

Post by zAy0LfpBZLC8mAC »

yawaramin wrote: post #68
cuu508 wrote: post #3

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 da…

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.

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.

Re: Working with time in Postgres

#74

Post by zAy0LfpBZLC8mAC »

Nullabillity wrote: post #22
deepsun wrote: post #8

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.

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

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.

Re: Working with time in Postgres

#75

Post by zAy0LfpBZLC8mAC »

Nullabillity wrote: post #24
0x0 wrote: post #15

Earlier quoted context omitted.

Any future event (such as a meeting) can't be stored as UTC, because time zone rules may change between now and the event date, but the event still needs to happen 10am local time.

Not really, that still breaks when you have participants from multiple time zones.

No, appointments for participants from multiple time zones are not specified in implicit local time, but rather in a some explicit timezone, often UTC, so you have to store that timezone instead of the implicit local one.

Re: Working with time in Postgres

#76

Post by oftenwrong »

Nullabillity wrote: post #22
deepsun wrote: post #8

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.

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

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

Re: Working with time in Postgres

#77

Post by manigandham »

openasocket wrote: post #72
manigandham wrote: post #57

Earlier quoted context omitted.

What doesn't sound right? You might be saying the same thing. To store this appointment correctly, you store the timezone of the appointment (let's say EST) and the timestamp within that timezone (4pm). So the final version is 16:00 EST. You can store a UTC version as well, but you need the local timestamp and timezone first. If you only store the timezone with a UTC timestamp, when you convert back after DST shifts…

No, I mean you make the appointment for December 3rd at 10:00am in Eastern Time (ET), and then you convert it to UTC to put into the database. Your local->UTC conversion library looks at the date and notices that this occurs during Daylight Savings Time for ET, and uses the correct offset when converting. Unless the rules about DST change between now and that meeting this is completely correct, you don't need to stor…

That is not reliable. Storing in UTC is optional, and can be used for easier comparisons, but you must store the local timezone and timestamp to be 100% reliable.

The local TZ => UTC formula may change for any reason, whether it's daylight saving time or any other random situation. It might be relatively stable in some locations (although it can change even within a global timezone like EST based on location) but there are constant updates being made. You can look at the IANA and Microsoft timezone database updates yourself. Why create risk when not necessary?

Re: Working with time in Postgres

#78

Post by manigandham »

Arnavion wrote: post #61
manigandham wrote: post #58

Earlier quoted context omitted.

> expected local timezone I'm not sure what this means. Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp. UTC version of that timestamp is optional (maybe for easier calculations in the database), but you must have the local timestamp to convert correctly. Local TZ => UTC is a formula that is not constant. You cannot store the output of this form…

>I'm not sure what this means. The timezone that the user will experience on that date. >Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp. Correct. I did not disagree with this. >Local TZ -> UTC is a formula that is not constant. You cannot store the output of this formula and expect to reverse and get back the original input precisely because of…

Yes, it is trivial extra work and storage for a timezone + timestamp (along with optional UTC) to get 100% reliable calculations.

Timezones, especially globally, have enough updates being made that they are not something worth the risk just to save a few bytes.

Re: Working with time in Postgres

#79

Post by jonknee »

zAy0LfpBZLC8mAC wrote: post #71
baddox wrote: post #65

Earlier quoted context omitted.

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

Just general calendar arithmetic. There are tons of cases where you need to do stuff like calculate "one month before", "one month after", "beginning of the year", ... --that's a mess with unix timestamps, but trivial with a good calendar library (or postgres, for that matter).

I agree with you (I don't use unix timestamps), but if you're using a good calendar library you can certainly get the result as a unix timestamp.

Re: Working with time in Postgres

#80

Post by combatentropy »

revicon wrote: post #20
barsonme wrote: post #17

Earlier quoted context omitted.

yeah, the comma after the "with" block shouldn't be there. i.e., ... weeks ) SELECT weeks.week

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

This should work. I'm sorry, I conformed it to my own style (lowercase and different indentation).

    with weeks as (
        select week
        from generate_series(
            '2017-01-01'::date,
            now()::date,
            '1 week'::interval
        ) week
    )
    select weeks.week, count(1)
    from weeks, test_results
    where
        test_results.date_created > weeks.week and
        test_results.date_created
Post reply on HN