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

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. +