Working with time in Postgres
craigkerstiens.com
Working with time in Postgres
1–10 of 128 posts
Re: Working with time in Postgres
#2Fantastic 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.
Re: Working with time in Postgres
#3Another 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.
Re: Working with time in Postgres
#4Fantastic 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.
Doh! Will fix.
Re: Working with time in Postgres
#5Small 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');
Re: Working with time in Postgres
#6Small 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');
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?
Re: Working with time in Postgres
#7Small 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');
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?
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 :)
Re: Working with time in Postgres
#8Last 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.
Re: Working with time in Postgres
#9Earlier quoted context omitted.
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?
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 :)
Will try to clarify it a bit further. Offhand you could probably do something like the following to find if it fell within some week range:
created_at BETWEEN (date_trunc('week', now())) AND (date_trunc('week', now() - '1 week'::interval))
Re: Working with time in Postgres
#10Last 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.
Would it be that much work to add a smallint field, that had the original UTC offset used for your time?