TIME column manipulation/comparison hangups

On 03/11/2008 19:01, Bill Moran wrote:

It seems as if EXTRACT() will work, but it sure feels hacky
to do:

(extract(hours from now()) = extract(hours from time_column)
AND
(extract(minutes from now()) = extract(minutes from time_column)

I'd have thought that this was the correct way to do it. Anyway, you
could encapsulate this in a function to make re-use easier (the
following hasn't been tested):

create function is_same_minute(time with time zone, time with time zone)
returns bool
as
$$
select
(extract(hours from $1) = extract(hours from $2))
and
(extract(minutes from $1) = extract(minutes from $2));
$$
language sql;

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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.

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 ...

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

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

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.

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.

In response to "Scott Marlowe" <scott.marlowe@gmail.com>:

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.

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@collaborativefusion.com
Phone: 412-422-3463x4023

Bill Moran <wmoran@collaborativefusion.com> writes:

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.

Well, actually there's no date_trunc for time either:

regression=# \df date_trunc
List of functions
Schema | Name | Result data type | Argument data types
------------+------------+-----------------------------+-----------------------------------
pg_catalog | date_trunc | interval | text, interval
pg_catalog | date_trunc | timestamp with time zone | text, timestamp with time zone
pg_catalog | date_trunc | timestamp without time zone | text, timestamp without time zone
(3 rows)

However, the interval version of the function can capture the time case
because there's an implicit cast from time to interval:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource = 'time'::regtype;
casttarget | castcontext | castfunc
------------------------+-------------+----------------------------------------
interval | i | "interval"(time without time zone)
time with time zone | i | timetz(time without time zone)
time without time zone | i | "time"(time without time zone,integer)
(3 rows)

There's no implicit cast from timetz to interval, which I suppose is
because it would be an information-losing transform.

regards, tom lane

Tom Lane escribi�:

However, the interval version of the function can capture the time case
because there's an implicit cast from time to interval:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource = 'time'::regtype;
casttarget | castcontext | castfunc
------------------------+-------------+----------------------------------------
interval | i | "interval"(time without time zone)
time with time zone | i | timetz(time without time zone)
time without time zone | i | "time"(time without time zone,integer)
(3 rows)

BTW it very much looks like we should have a pg_casts view that displays
these things in a human-readable manner (like the above except with
castcontext expanded)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribi�:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource = 'time'::regtype;

BTW it very much looks like we should have a pg_casts view that displays
these things in a human-readable manner (like the above except with
castcontext expanded)

There already is a \dC command in psql, which has nice enough output
format but doesn't provide any way to select a subset of the table.
Maybe we should just agree that its argument is a pattern for the
castsource type's name?

regards, tom lane

Tom Lane escribió:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribi�:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource = 'time'::regtype;

BTW it very much looks like we should have a pg_casts view that displays
these things in a human-readable manner (like the above except with
castcontext expanded)

There already is a \dC command in psql, which has nice enough output
format but doesn't provide any way to select a subset of the table.
Maybe we should just agree that its argument is a pattern for the
castsource type's name?

Yeah, that sounds good enough ... I seem to recall having used
casttarget as condition a couple of times, but I think it's a strange
enough case that it is OK to just modify the query when that's needed;
normal usage would seem to be what you propose.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribió:

There already is a \dC command in psql, which has nice enough output
format but doesn't provide any way to select a subset of the table.
Maybe we should just agree that its argument is a pattern for the
castsource type's name?

Yeah, that sounds good enough ... I seem to recall having used
casttarget as condition a couple of times, but I think it's a strange
enough case that it is OK to just modify the query when that's needed;
normal usage would seem to be what you propose.

Here's a draft patch for this. One possible objection is that the
default behavior changes subtly: only casts whose source types are
visible in the search path will be shown by default. In practice
I doubt that will make any difference, so I didn't bother to try to
avoid it --- we could special-case no pattern but I think it'd look
like a wart before long.

Comments?

regards, tom lane

Alvaro Herrera wrote:

Tom Lane escribi�:

However, the interval version of the function can capture the time case
because there's an implicit cast from time to interval:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource = 'time'::regtype;
casttarget | castcontext | castfunc
------------------------+-------------+----------------------------------------
interval | i | "interval"(time without time zone)
time with time zone | i | timetz(time without time zone)
time without time zone | i | "time"(time without time zone,integer)
(3 rows)

BTW it very much looks like we should have a pg_casts view that displays
these things in a human-readable manner (like the above except with
castcontext expanded)

Could we change the data types of the pg_cast table to regprocedure and
regtype instead?

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane escribi�:

regression=# select casttarget::regtype,castcontext,castfunc::regprocedure from pg_cast where castsource = 'time'::regtype;

BTW it very much looks like we should have a pg_casts view that displays
these things in a human-readable manner (like the above except with
castcontext expanded)

There already is a \dC command in psql, which has nice enough output
format but doesn't provide any way to select a subset of the table.
Maybe we should just agree that its argument is a pattern for the
castsource type's name?

I'd say it could be a pattern for both source and target. Often times I
am interested in casts in either direction.

Peter Eisentraut <peter_e@gmx.net> writes:

Could we change the data types of the pg_cast table to regprocedure and
regtype instead?

Back when we first introduced the reg-foo types, there was some
discussion of changing all relevant catalog columns to those types,
but the idea crashed and burned for reasons I don't recall right
at the moment.

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane wrote:

Maybe we should just agree that its argument is a pattern for the
castsource type's name?

I'd say it could be a pattern for both source and target. Often times I
am interested in casts in either direction.

Well, it makes the query markedly uglier, but I suppose we aren't too
concerned about the performance of \dC. New proposed patch attached.

regards, tom lane