Обсуждение: BUG #7886: date_trunc(date) returning timestamptz instead of timestamp

The following bug has been logged on the website:

Bug reference:      7886
Logged by:          Nick Baxter
Email address:      nick.baxter@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)

On Fri, Feb 15, 2013 at 09:27:40PM +0000, nick.baxter@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      7886
> Logged by:          Nick Baxter
> Email address:      nick.baxter@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)

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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:
> On Fri, Feb 15, 2013 at 09:27:40PM +0000, nick.baxter@gmail.com wrote:
>> 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