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


Thu, 20 Mar 2003 21:36:53 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
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


Thu, 20 Mar 2003 13:34:13 -0500 (EST)
Darren Ferguson <darren@thread.crystalballinc.com>
Re: Extracting time from timestamp
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

Fri, 21 Mar 2003 08:59:39 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Re: Extracting time from timestamp
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

Fri, 21 Mar 2003 11:27:53 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Re: Extracting time from timestamp
> 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


Fri, 21 Mar 2003 14:08:27 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [HACKERS] Extracting time from timestamp
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

Fri, 21 Mar 2003 11:43:29 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Re: [HACKERS] Extracting time from timestamp
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


Sun, 23 Mar 2003 08:01:13 -0800 (PST)
Chris Gamache <cgg007@yahoo.com>
Re: [HACKERS] Extracting time from timestamp