Обсуждение: Extracting time from timestamp
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
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
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
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
> 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
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