From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Bill Totman <totman(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: timestamp to date and time column migration |
Date: | 2007-07-29 23:26:40 |
Message-ID: | 6375221C-0732-4A50-8560-0BE5BA31A281@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Totman | 2007-07-30 02:12:05 | Re: timestamp to date and time column migration |
Previous Message | Bill Totman | 2007-07-29 20:56:41 | Re: timestamp to date and time column migration |