I'm tearing my hair out trying to understand time zones.
I want to use date_trunc to find the start and end of last day, week, month
at a remote site.
I looked at the date_trunc docs and they say
function date_trunc(text, timestamp)
Return Type timestamp
Description Truncate to specified precision;
see also Section 9.9.2
Example date_trunc('hour', timestamp '2001-02-16 20:38:40')
Result 2001-02-16 20:00:00
Now I took that to mean that the function takes a parameter of type
"timestamp without time zone", since the word timestamp along is these days
a synonym for that.
however, if I put this query into pgAdmin
SELECT date_trunc( 'WEEK' , (timestamp without time zone '2011-06-20
10:30 US/Hawaii')
results in date_trunc --- timestamp without time zone "2011-06-20
00:00:00"
but
SELECT date_trunc( 'WEEK' , (timestamp with time zone '2011-06-20
10:30 US/Hawaii')
results in date_trunc --- timestamp with time zone "2011-06-20
00:00:00+01"
So it looks as if date_trunc is taking either type and returning a value of
the same type.
A perfectly reasonable thing to do - but does that correspond with the
documentation?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/date-trunc-not-sure-what-is-wrong-but-it-confuses-me-tp4716052p4716052.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.