Postgresql – How to truncate a `date` (not `timestamptz`) to month

postgresqlpostgresql-9.3timezone

Postgres has date_trunc which operates on timestamp or interval, and:

Values of type date and time are cast automatically to timestamp or interval, respectively.

In other words we can use date_trunc for date values with a cast:

select date_trunc('month',current_date)::date;
┌────────────┐
│ date_trunc │
├────────────┤
│ 2014-12-01 │
└────────────┘

But is this timezone safe—if the current date is in Daylight Saving Time and the start of the month is not or vice versa will the correct date result?

Best Answer

Yes it's safe.

It's not exactly clear in the documentation but date_trunc can return either timestamp or timestamptz depending on what you pass it. If you pass a date, it will cast the input to timestamp and returns timestamp so there can be no tz issue when casting back to date.

If you pass a timestamptz it does the truncation the way you'd expect and returns a timestamptz:

select current_timestamp-'40d'::interval;
┌───────────────────────────────┐
│           ?column?            │
├───────────────────────────────┤
│ 2014-10-27 19:32:13.869909+00 │
└───────────────────────────────┘

select date_trunc('month',current_timestamp-'40d'::interval);
┌────────────────────────┐
│       date_trunc       │
├────────────────────────┤
│ 2014-10-01 00:00:00+01 │
└────────────────────────┘
Related Question