Thread: Select date & time

Hello,

   I have a table in which I have three columns (for reasons I will not
go into here): Date, time and time (milliseconds). However, I want to
start using timestamps instead. Problem is, I don't know how to get the
data in a proper format using selects.

Say I have a table:

> create table foo (ts timestamp)

..and I insert an entry:

> insert into table foo (ts) VALUES (now())

This will yield an entry which looks something like:

           ts
---------------------------
2006-02-04 19:27:31.123456

   I'm interested in retaining this information in the database, but not
when I'm going to present it. I.e. I want to be able to create a query
that will return:

    date   |   time   |
-----------+----------+----
2006-02-04 | 19:27:31 |

I figured out the date part:

> select date(ts) from foo

But how do I get the "time" part? Thankful for any tips.

--
Kind Regards,
Jan Danielsson
Te audire non possum. Musa sapientum fixa est in aure.

Jan Danielsson <jan.danielsson@gmail.com> schrieb:
> This will yield an entry which looks something like:
>
>            ts
> ---------------------------
> 2006-02-04 19:27:31.123456
>
>    I'm interested in retaining this information in the database, but not
> when I'm going to present it. I.e. I want to be able to create a query
> that will return:
>
>     date   |   time   |
> -----------+----------+----
> 2006-02-04 | 19:27:31 |

Read the manual about to_char:
http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html

and

http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°