date_trun() with timezones? (was Re: TIME column manipulation/comparison hangups)

In response to "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>:

> On Mon, Nov 3, 2008 at 12:25 PM, Bill Moran
> <wmoran(at)collaborativefusion(dot)com> wrote:
> > In response to "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>:
> >
> >> On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
> >> <wmoran(at)collaborativefusion(dot)com> wrote:
> >> >
> >> > I'm trying to test the time in a time column to see if it's the same
> >> > minute as the current time. I wouldn't have thought this would be
> >> > difficult:
> >> >
> >> > WHERE TO_CHAR(now(), 'HH24MI') = TO_CHAR(time_column, 'HH24MI')
> >>
> >> Use date_trunc
> >>
> >> where date_trunc('minute',timefield)=date_trunc('minute',now());
> >>
> >> I might have the args backwards.
> >
> > Hunh ...
> >
> > # select date_trunc('minute','13:45:15'::time);
> > date_trunc
> > --------------------
> > @ 13 hours 45 mins
> > (1 row)
> >
> > # select date_trunc('minute','13:45:15'::time with time zone);
> > ERROR: function date_trunc(unknown, time with time zone) does not exist
> > LINE 1: select date_trunc('minute','13:45:15'::time with time zone);
> > ^
> > HINT: No function matches the given name and argument types. You might need to add explicit type casts.
> >
> > # select date_trunc('minute',('13:45:15'::time with time zone)::time);
> > date_trunc
> > --------------------
> > @ 13 hours 45 mins
> > (1 row)
> >
> > Curiouser and curiouser ...
>
> Ahhh, not timestamps, but times... You might have to add the time to
> some date to run it through date_trunc.

Not quite. As shown in the examples, date_trunc() works fine on
TIME WITHOUT TIME ZONE, but there's no such function for TIME WITH
TIME ZONE.

Is that an oversight, or does the timezone add some ambiguity that
date_trunc() can't handle?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023