Extracting time from timestamp

If the field is definately a timestamp field just do the following

SELECT TO_CHAR(stime,'HH24:MI') FROM bookings;

This will give you the desired answer

Darren

On Thu, 20 Mar 2003, Shridhar Daithankar<shridhar_daithankar@persistent.co.in> wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Darren Ferguson

On Friday 21 Mar 2003 12:04 am, Darren Ferguson wrote:

If the field is definately a timestamp field just do the following

SELECT TO_CHAR(stime,'HH24:MI') FROM bookings;

Problem is I want timestamp out of it, not char. representation because I have
to compare quite a few of them. That's where I am stuck..

Shridhar

On Thursday 20 Mar 2003 9:36 pm, Shridhar
Daithankar<shridhar_daithankar@persistent.co.in> wrote:

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.

After much of RTFm( \df in psql in fact ), I found the solution. It is
timetz(abstime(timestamp)).

Well, timezone is OK with me but if somebody needs no timezones, then it is
still screwed though..

Further more, \df I find following output

time without time zone | pg_catalog | time| abstime
time without time zone | pg_catalog | time| interval
time without time zone | pg_catalog | time| text
time without time zone | pg_catalog | time| time with time zone
time without time zone | pg_catalog | time| time without time zone,
integer
time without time zone | pg_catalog | time| timestamp with time zone
time without time zone | pg_catalog | time| timestamp without time zone

I don't found these functions working as they expected. e.g.

phd=# select time(abstime(timestamp 'now')) from bookings;
ERROR: parser: parse error at or near "abstime" at character 13
phd=# select time(timestamp 'now') from bookings;
ERROR: parser: parse error at or near "timestamp" at character 13
phd=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
(1 row)

That goes for any timestamp value I presume. Is this a bug or am I
misinterpreting the information?

Shridhar

phd=# select time(abstime(timestamp 'now')) from bookings;
ERROR: parser: parse error at or near "abstime" at character 13
phd=# select time(timestamp 'now') from bookings;
ERROR: parser: parse error at or near "timestamp" at character 13
phd=# select version();
version

Try:

select "time"(abstime(timestamp 'now')) from bookings;
select "time"(timestamp 'now') from bookings;

Chris

On Friday 21 Mar 2003 11:38 am, Christopher Kings-Lynne wrote:

phd=# select time(abstime(timestamp 'now')) from bookings;
ERROR: parser: parse error at or near "abstime" at character 13
phd=# select time(timestamp 'now') from bookings;
ERROR: parser: parse error at or near "timestamp" at character 13
phd=# select version();
version

Try:

select "time"(abstime(timestamp 'now')) from bookings;
select "time"(timestamp 'now') from bookings;

First of all, thanks, it worked..

And What's so holy about "" if it is a function?

That was bummer, I admit.. Spent almost a day on it..

Shridhar

On Friday 21 Mar 2003 11:38 am, Christopher Kings-Lynne wrote:

phd=# select time(abstime(timestamp 'now')) from bookings;
ERROR: parser: parse error at or near "abstime" at character 13
phd=# select time(timestamp 'now') from bookings;
ERROR: parser: parse error at or near "timestamp" at character 13
phd=# select version();
version

Try:

select "time"(abstime(timestamp 'now')) from bookings;
select "time"(timestamp 'now') from bookings;

First of all, thanks, it worked..

And What's so holy about "" if it is a function?

That was bummer, I admit.. Spent almost a day on it..

Shridhar

P.S. Sorry, I didn't realized that I handn' CC hackers..

select "time"(abstime(timestamp 'now')) from bookings;
select "time"(timestamp 'now') from bookings;

First of all, thanks, it worked..

And What's so holy about "" if it is a function?

It's really old 7.1 syntax, not supported from 7.2+.

Basically it's because time can now have a precision. eg. a field of type
TIME(4) will have decimal places of millisecond precision. You need to
quote the function to make it get treated as a function rather than a type
definition...

A better (standard) way to express it is probably:

select cast(cast(current_timestamp as abstime) as time) from bookings;

or even

select current_timestamp::abstime::time from bookings;

Chris

"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>" <shridhar_daithankar@persistent.co.in> writes:

And What's so holy about "" if it is a function?

The problem is that TIME(n) is a datatype name, not a function call,
according to the SQL spec. Likewise for TIMESTAMP(n), INTERVAL(n),
NUMERIC(m,n), and maybe one or two other special cases I've forgotten.

The SQL spec's love of special-purpose syntaxes is one of its worst
features IMHO ...

regards, tom lane

On Friday 21 Mar 2003 12:25 pm, Tom Lane wrote:

"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"

<shridhar_daithankar@persistent.co.in> writes:

And What's so holy about "" if it is a function?

The problem is that TIME(n) is a datatype name, not a function call,
according to the SQL spec. Likewise for TIMESTAMP(n), INTERVAL(n),
NUMERIC(m,n), and maybe one or two other special cases I've forgotten.

The SQL spec's love of special-purpose syntaxes is one of its worst
features IMHO ...

In this case, I would vote for overload as SQL extension in postgresql if
people feel it is feasible and/or sensible..

Shridhar

Why not a cast?

template1=# select current_timestamp::time;
time
-----------------
11:24:22.004207
(1 row)

template1=# select current_timestamp::time(0);
time
----------
11:24:26
(1 row)

--- Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:

phd=# select time(abstime(timestamp 'now')) from bookings;
ERROR: parser: parse error at or near "abstime" at character 13
phd=# select time(timestamp 'now') from bookings;
ERROR: parser: parse error at or near "timestamp" at character 13
phd=# select version();
version

Try:

select "time"(abstime(timestamp 'now')) from bookings;
select "time"(timestamp 'now') from bookings;

Chris

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com