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.