timezone aware date_trunc function

Question

The following query

SELECT the_date FROM date_trunc('day', timestamp with time zone 
       '2001-01-1 00:00:00+0100') as the_date

results to

the_date
2000-12-31 00:00

Is there a way to tell date_trunc to do day/month/year conversions based on the timezone it is feeded with?

The expected output would be: 2001-01-1 00:00+0100

I think you're misunderstanding `timestamp with time zone`. (That's common, it's a stupid name for the type's actual semantics). `timestamp with time zone` is not in fact a timestamp with a time zone. It's a timestamp stored in UTC that's converted to/from the `TimeZone` of the client on I/O. — Craig Ringer, Jun 04 '14 at 14:36
There is a bug in your question. What you call a "date" (`the_date`) actually has the form of a `timestamp` and really should be a `timestamptz`, since the query displayed would return that. We do not know how that result was cooked. Also, the meaning of the timestamp is ambiguous without knowing your local time zone. — Erwin Brandstetter, Jun 04 '14 at 15:28
@ErwinBrandstetter I agree that "the_date" is misleading in this context. But what has my local timezone to do with the question? — Jay, Jun 04 '14 at 16:12
The *meaning* of a `timestamp` value depends on your current time zone setting in your client. This may be of help: http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170 — Erwin Brandstetter, Jun 04 '14 at 17:13

score 23 · Accepted Answer · answered Jun 04 '14 at 14:34

23

You need to specify at which time zone you want it to show

select
    date_trunc(
        'day',
        timestamp with time zone '2001-01-1 00:00:00+0100' at time zone '-02'
    ) as the_date;
      the_date       
---------------------
 2001-01-01 00:00:00

AT TIME ZONE

answered Jun 04 '14 at 14:34

Clodoaldo Neto

118,695
26
233
260

This fixes the lower end. But what is with "2001-12-31 24:00:00+0100" > 2002-01-01 00:00:00? – Jay Jun 04 '14 at 15:24
@Jay midnight is a special case: *can* be referenced from the previous day, but always belongs to the following day. i.e: `select timestamptz '2001-12-31 24:00:00+00'` will yield `2002-01-01 00:00:00+00` – pozs Jun 05 '14 at 08:04
Note that for this answer to make sense, your Postgres database timezone needs to be set correctly. See its current value with `SHOW timezone;` and change its value with e.g: `ALTER DATABASE SET timezone TO 'UTC';`. More info here: https://stackoverflow.com/questions/6663765/postgres-default-timezone – stwr667 Feb 02 '21 at 13:49

score 23 · Answer 2 · answered Feb 28 '18 at 14:33

While the marked answer might be correct for the OP's weird circumstances it is more likely incorrect for others. You need to convert the timestamp returned by date_trunc to the proper timezone.

select
    date_trunc(
        'day',
        some_timestamp at time zone users_timezone
    ) at time zone users_timezone as the_date;

The important thing to understand is date_trunc returns a timestamp with no timezone attached to it. You need to convert the timestamp to the proper timezone because the database client or whatever downstream might have a different timezone.

```at time zone users_timezone as the_date;``` is what saved me. Thanks man! — Rami, Aug 07 '23 at 02:40

score 4 · Answer 3 · answered Oct 28 '19 at 02:45

@Adam's answer is definitely more helpful. Although I think we can improve on it again because if we're truncating a Timestamp to a single day (or week/month/etc), then we want to make sure that we're dealing with a Date object, not a Timestamp. Otherwise we may give other pieces of code the impression that something just actually happened to occur at midnight (or potentially some other misleading time of day).

So I would use:

SELECT date_trunc('day', some_timestamp AT TIME ZONE users_timezone)::date AS the_date;

which casts the result to a Date, rather than Timestamp.

The result will be something like:

  the_date
------------
 2019-09-14

instead of the more misleading result of:

      the_date
---------------------
 2019-09-14 00:00:00

timezone aware date_trunc function

3 Answers3

Linked