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

Answer 1

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

Answer 2

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.