Thread: BUG #7886: date_trunc(date) returning timestamptz instead of timestamp
BUG #7886: date_trunc(date) returning timestamptz instead of timestamp
From
nick.baxter@gmail.com
Date:
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