Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Re: timestamp to date and time column migration




On Jul 29, 2007, at 15:56 , Bill Totman wrote:

On Sunday 29 July 2007 15:20, you wrote:
I created a table where I used a timestamp type column and after
inserting
about 300 entries into this table I would now rather have two separate
columns for that data: one for just the date and the other for just
the time.

Why? What problem are you trying to solve?

I was wanting to make it simple to select entries by time (of day).

Is there a function that will do the same?

I believe casting the timestamp to time (or timetz) and date will do what you want:

test=# select current_timestamp, current_timestamp::timetz, current_timestamp::date;
              now              |        now         |    now
-------------------------------+--------------------+------------
2007-07-29 18:16:49.643542-05 | 18:16:49.643542-05 | 2007-07-29
(1 row)

If both date and time are important, I'd recommend keeping them in a timestamp and decomposing when you need to. Depending on what kinds of queries are performed, you may also want to look into using expressional indexes, such as:

CREATE INDEX timestamptz_col_date_idx ON foo (timestamptz_col::date);
CREATE INDEX timestamptz_col_timetz_idx ON foo (timestamptz_col::timetz);

Hope this helps.

Michael Glaesemann
grzm seespotcode net





Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group