How to extract consistent year from timestamptz

postgresql, sql, timestamp-with-timezone, timezone

Solution

Use `at time zone`

create table foo (
    t timestamptz
    check (extract(year from t at time zone 'UTC') = 2013)
);

select extract(year from
    '01-01-1970 00:00:00 utc+01'::timestamp with time zone at time zone 'utc'
    );
 date_part 
-----------
      1970

Problem

I'm trying to use the Postgres function `extract()` to get the year from a `timestamptz` column, but get unexpected results. I expected that it would use UTC, but appears to use whatever the local timezone of the system is (EST in my case). How do I get the `extract` function to return UTC regardless of what the timezone of the timestamp or the system is? Example 1: ``` testdb=# create table foo ( t timestamptz check( extract(year from t)::int = 2013) ); testdb=# \d foo Table "public.foo" Column | Type | Modifiers --------+--------------------------+----------- t | timestamp with time zone | Check constraints: "foo_t_check" CHECK (date_part('year'::text, t)::integer = 2013) testdb=# insert into foo values ('2013-01-01 01:49:05.048+00'); ERROR: new row for relation "foo" violates check constraint "foo_t_check" DETAIL: Failing row contains (2012-12-31 20:49:05.048-05). ``` Example 2: ``` testdb=# SELECT EXTRACT(year FROM '01-01-1970 00:00:00 UTC+01'::timestamp with time zone); date_part ----------- 1969 (1 row) ```

Original source