From: Tom Lane <tgl@sss.pgh.pa.us>
To: norvelle@ag.arizona.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: Trying to convert a TIMESTAMP return value to TIME
Date: Thu, 21 Feb 2002 21:07:14 -0500
Message-ID: <12972.1014343634@sss.pgh.pa.us> (raw)
In-Reply-To: <48367cfb.0202201935.3dc51c8a@posting.google.com>
References: <48367cfb.0202201935.3dc51c8a@posting.google.com>
norvelle@ag.arizona.edu (Erik Norvelle) writes:
> I get the following error: 'column "event_time" is of type 'time
> without time zone' but expression is of type 'timestamp with time
> zone' You will need to rewrite or cast the expression'.
> I have looked extensively at the PostgreSQL documentation, and have
> failed to find any info on how to get a TIME value out of a TIMESTAMP,
> or else how to convert a CHAR time like '10:45' directly to a TIME.
A little bit of experimentation found that you can get there indirectly
by casting first to time-with-time-zone:
regression=# select TO_TIMESTAMP('10:45', 'HH:MI');
to_timestamp
---------------------
0001-01-01 10:45 BC
(1 row)
regression=# select TO_TIMESTAMP('10:45', 'HH:MI')::time;
ERROR: Cannot cast type 'timestamp with time zone' to 'time without time zone'
regression=# select TO_TIMESTAMP('10:45', 'HH:MI')::timetz;
to_timestamp
--------------
10:45:00+00
(1 row)
regression=# select TO_TIMESTAMP('10:45', 'HH:MI')::timetz::time;
to_timestamp
--------------
10:45:00
(1 row)
However, if you don't need the formatting control of to_timestamp
(ie, the input data should be valid time input anyway), why not
forget to_timestamp and just cast text to time?
regression=# select '10:45'::text::time;
time
----------
10:45:00
(1 row)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, norvelle@ag.arizona.edu
Subject: Re: Trying to convert a TIMESTAMP return value to TIME
In-Reply-To: <12972.1014343634@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox