Thread: Constructors for dates, times, and timestamps

Migrating from DB/2 6.1 to PostgreSQL 8.1.4,

The following work under DB/2, but I can find no analog in the
PostgreSQL documentation:

    time('00:00:00')  [there is to_date() and to_timestamp(), but no
to_time()?]

    timestamp(u.date, u.time) [where u.date is of type DATE and u.time
is of type TIME]

As with any paradigm shift, I'm sure this will be embarrassingly simple,
but I've spent hours RTFMing to no avail.

Andy

"Andrew T. Robinson" <atr@nmi.net> writes:
> The following work under DB/2, but I can find no analog in the
> PostgreSQL documentation:

>     time('00:00:00')  [there is to_date() and to_timestamp(), but no
> to_time()?]

Write it as a cast, either SQL-spec CAST() or PG :: notation.

regression=# select '00:00:00'::time;
   time
----------
 00:00:00
(1 row)

In many situations PG also accepts the same function-like notation for
specifying casts that DB/2 seems to be using, but in this particular
case it doesn't work because TIME(n) is a datatype specification
according to the SQL spec, and the special syntax needed for that
conflicts with this usage.

>     timestamp(u.date, u.time) [where u.date is of type DATE and u.time
> is of type TIME]

You can add a date and a time to get a timestamp:

regression=# select '3-1-2007'::date + '12:34'::time;
      ?column?
---------------------
 2007-03-01 12:34:00
(1 row)

            regards, tom lane

On Tue, Feb 27, 2007 at 07:47:32AM -0500, Andrew T. Robinson wrote:
> Migrating from DB/2 6.1 to PostgreSQL 8.1.4,
>
> The following work under DB/2, but I can find no analog in the
> PostgreSQL documentation:
>
>    time('00:00:00')  [there is to_date() and to_timestamp(), but no
> to_time()?]

Well, you can always use to_timestamp and then cast to time, but this
also works:

# select "time"('00:00:00');
   time
----------
 00:00:00
(1 row)

I can't explain the need for the quotes, some kind of grammer issue.

>    timestamp(u.date, u.time) [where u.date is of type DATE and u.time
> is of type TIME]

There is the function datetime_pl(date,time) whic does that, but most
people just use +'

# select '2007-05-02'::date + '22:33:44'::time;
      ?column?
---------------------
 2007-05-02 22:33:44
(1 row)

You can wrap it into a simple function if that makes it easier to
understand.

Oddly, the documentation indeed doesn't list all the functions, but the
operators will do what you want also.

http://www.postgresql.org/docs/current/static/functions-datetime.html

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Thanks for the pointers.  I did figure out

    'yyyy-mm-dd'::date
    'hh:mm:ss'::time

And I also came up with

    (date_column::text || ' ' || time_column::text)::timestamp

Which is too ugly for words.  I appreciate the more elegant solutions
posted to the list.

I'm growing to like Postgres, but I'm not sure I'll ever get over DB/2 :-)

Andy