Ludwig Isaac Lim wrote:
>
> --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> > > I notice that when I type \df to_char in psql prompt
> > the following does
> > > not appear as one of the possible arguments of to_char:
> > >
> > > time without time zone,text
> > >
> > > However, the following select statement works:
> > >
> > > SELECT to_char(current_time :: 'time without time
> > zone', 'ss');
> > >
> > > Is this a bug?
...
> ------------+---------+------------------+-----------------------------------
> > ...
> > pg_catalog | to_char | text | timestamp
> > without time zone, text
> >
>
> I did saw that, but I think was thinking "time without
> time zone" is not the same as "timestamp without time
> zone". I was just thinking that it might be misleading to
> some person.
Oh, I missed that you were saying "time" and not "timezone". Hmm.
Seems current_timestamp has a timezone and that fails:
test=> SELECT to_char(current_time, 'hh');
ERROR: function to_char(time with time zone, "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
as does 'time with time zone':
test=> SELECT to_char(current_time :: time with time zone, 'hh');
ERROR: function to_char(time with time zone, "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
but with _no_ time zone it works:
test=> SELECT to_char(current_time :: time without time zone, 'hh');
to_char
---------
08
(1 row)
test=> SELECT to_char(current_time :: time, 'hh');
to_char
---------
08
(1 row)
The reason to_char() works is because of the auto-conversion from time
(no tz) to interval (1083 is the 'time' data type):
test=> select typname from pg_cast, pg_type where castsource = 1083 and
casttarget = pg_type.oid;
typname
----------
text
bpchar
varchar
time
interval <----
timetz
(6 rows)
and you will see to_char() has an interval match:
test=> \df to_char
List of functions
Schema | Name | Result data type | Argument data types
------------+---------+------------------+-----------------------------------
pg_catalog | to_char | text | bigint, text
pg_catalog | to_char | text | double precision, text
pg_catalog | to_char | text | integer, text
pg_catalog | to_char | text | interval, text
pg_catalog | to_char | text | numeric, text
pg_catalog | to_char | text | real, text
pg_catalog | to_char | text | timestamp with time zone text
pg_catalog | to_char | text | timestamp without time zone,
text
(8 rows)
and I see that working below:
test=> SELECT to_char(current_time :: time :: interval, 'hh');
to_char
---------
08
(1 row)
So, basically what is happening is the time is being stripped of the
timezone, then the time is being converted to just hours/minutes/seconds
(an interval) and then passed to to_char().
With that analysis, I think you can see why the 'time zone' doesn't work
for this case.
So, in fact here is no literl 'time without time zone' match in
to_char() but rather an automatic type conversion.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
