Re: date_trunc() in a specific time zone

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: vik(dot)fearing(at)2ndquadrant(dot)com
Cc: Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com>, Steve Crawford <scrawford(at)pinpointresearch(dot)com>, andreas(at)proxel(dot)se, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: date_trunc() in a specific time zone
Date: 2018-11-13 23:20:30
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I wrote:
> BTW, I'd been hoping that we could avoid rotate-to-local-and-back
> in Vik's desired case, but after further thought I suspect the only
> real optimization that's possible compared to writing it out with
> two AT TIME ZONE constructs is to do the zone name lookup just once.
> As an example, truncating to a day-or-larger boundary could result in
> shifting to a different UTC offset than you started with, due to crossing
> a DST boundary.

Here's a v2 that transposes the code to C so that we can get that
optimization. I've not tried to time it, but it should actually be
a bit faster than standard date_trunc plus one AT TIME ZONE rotation,
never mind two of them.

regards, tom lane

Attachment Content-Type Size
date_trunc_tz_v2.patch text/x-diff 11.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2018-11-13 23:30:21 Re: TupleTableSlot abstraction
Previous Message Tom Lane 2018-11-13 23:01:46 Re: add the source of param misconfigurations to error messages