On Mon, Nov 3, 2008 at 12:31 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Nov 3, 2008 at 12:25 PM, Bill Moran
> <wmoran@collaborativefusion.com> wrote:
>> In response to "Scott Marlowe" <scott.marlowe@gmail.com>:
>>
>>> On Mon, Nov 3, 2008 at 12:01 PM, Bill Moran
>>> <wmoran@collaborativefusion.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.
Actually, the more I look at this the more I think extract / date_part
might be your best answer.