Extracting time from timestamp

From: "Shridhar Daithankar<shridhar_daithankar(at)persistent(dot)co(dot)in>" <shridhar_daithankar(at)persistent(dot)co(dot)in>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Extracting time from timestamp
Date: 2003-03-20 16:06:53
Message-ID: 200303202136.53992.shridhar_daithankar@persistent.co.in
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hi,

I know this is rather stupid but still,

I have a table which has a timestamp field in it and I need to get only time
part of it. i.e. HH:MI format.

So far I tried,

phd=# select to_timestamp( to_char(stime,'HH24:MI'),'HH24:MI') from bookings;
to_timestamp
------------------------
0001-01-01 04:30:00 BC
0001-01-01 04:30:00 BC
0001-01-01 04:30:00 BC
(3 rows)

I don't know where that BC crept in. It does not show up when I just select
stime from bookings;

I also tried

phd=# select timestamp to_char(stime,'HH24:MI'),'HH24:MI' from bookings;
ERROR: parser: parse error at or near "to_char" at character 18

To me that looks like casting a text returned by to_char to timestamp. This
casting should work if I infer from things like to_char(timestamp
'now','HH12:MI:SS') mentioned in postgresql manual(Data type formatting
function, section 6.7).

Being very stupid, is there any more efficient way of doing this?

TIA..

Shridhar

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-03-20 16:20:39 Re: PostgreSQL downloads compressed with bzip2 instead
Previous Message John Duffy 2003-03-20 16:03:50 Accessing function parameters within double quotes

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2003-03-20 16:09:18 Re: to_char support for intervals
Previous Message Peter Eisentraut 2003-03-20 16:04:38 Re: More outdated examples