date_trunc - not sure what is wrong, but it confuses me.

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.