BUG #7886: date_trunc(date) returning timestamptz instead of timestamp

[n***@gmail.com]

The following bug has been logged on the website:

Bug reference: 7886
Logged by: Nick Baxter
Email address: ***@gmail.com
PostgreSQL version: 9.0.3
Operating system: Linux 2.6.18
Description:

9.9.2. indicates that date_trunc can be called with a date value (which will
be cast to a timestamp). And regardless of the input, that the result will
be of type timestamp. When I call it with a date, I get a timestamp with
time zone instead, as indicated by the psql output.

# select date_trunc('month',date '2013-2-15');
date_trunc
------------------------
2013-02-01 00:00:00-06
(1 row)

[Bruce Momjian]

Post by n***@gmail.com
Bug reference: 7886
Logged by: Nick Baxter
PostgreSQL version: 9.0.3
Operating system: Linux 2.6.18
9.9.2. indicates that date_trunc can be called with a date value (which will
be cast to a timestamp). And regardless of the input, that the result will
be of type timestamp. When I call it with a date, I get a timestamp with
time zone instead, as indicated by the psql output.
# select date_trunc('month',date '2013-2-15');
date_trunc
------------------------
2013-02-01 00:00:00-06
(1 row)

That documentation often uses timestamp when it means timestamp with
time zone. Not sure why that is.

\df shows the supported functions:

test=> \df date_trunc
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+-----------------------------+-----------------------------------+--------
pg_catalog | date_trunc | interval | text, interval | normal
pg_catalog | date_trunc | timestamp without time zone | text, timestamp without time zone | normal
pg_catalog | date_trunc | timestamp with time zone | text, timestamp with time zone | normal

This returns a timestamp without time zone:

test=> select date_trunc('month',timestamp '2013-2-15');
date_trunc
---------------------
2013-02-01 00:00:00
(1 row)
--
Bruce Momjian <***@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

[Tom Lane]

Post by Bruce Momjian

Post by n***@gmail.com
9.9.2. indicates that date_trunc can be called with a date value (which will
be cast to a timestamp). And regardless of the input, that the result will
be of type timestamp. When I call it with a date, I get a timestamp with
time zone instead, as indicated by the psql output.

That documentation often uses timestamp when it means timestamp with
time zone. Not sure why that is.

Well, what the subsection about date_trunc says is "source is a value
expression of type timestamp or interval". Up at the very top of the
page, it says

All the functions and operators described below that take time
or timestamp inputs actually come in two variants: one that
takes time with time zone or timestamp with time zone, and one
that takes time without time zone or timestamp without time
zone. For brevity, these variants are not shown separately.

So omitting mention of date_trunc(timestamptz) is not inconsistent.
If we wanted to fix that the page would get quite a bit longer, but
perhaps not much more illuminating.

It strikes me that the problem is in the parenthetical remark in 9.9.2:
"(Values of type date and time are cast automatically to timestamp or
interval, respectively.)". Since there are both timestamp and
timestamptz alternatives available, the parser will actually prefer to
cast a date input to timestamptz, that being the preferred type in this
category. Maybe we should say "timestamp with time zone" there, though
I can see that confusing people in a different way.

regards, tom lane