Thread: Re: [GENERAL] Extracting time from timestamp

Re: [GENERAL] Extracting time from timestamp

From

"Shridhar Daithankar"

Date:

21 March 2003, 03:57:03

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

Re: [GENERAL] Extracting time from timestamp

From

"Christopher Kings-Lynne"

Date:

21 March 2003, 04:08:14

> 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

Re: [GENERAL] Extracting time from timestamp

From

Chris Gamache

Date:

23 March 2003, 17:05:11

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