Thread: timestamp to date and time column migration

Hello all,

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.

I'm not sure what the best way would be to:
1) backup that information (from the current timestamp column),
2) drop the timestamp table,
3) add a date type table and a time type table,
4) and ultimately import the already-space-separated timestamp data into these
two columns.

Barring the above outline being erroneous, I suppose I just to know if there
are any 'gotchas' that I should beware/know about before proceeding with this
migration... as well as the syntax for piping data or running INSERT commands
from a file on the commandline.


Thank you very, very much for any and all help you might have.

Bill Totman

On Jul 29, 2007, at 13:39 , Bill Totman 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?

Michael Glaesemann
grzm seespotcode net

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 have been able to copy the database to another name and with the data
portion of a backup of the original and make the desired changes there. I
wasn't going to make the changes to the 'production' database until I heard
from the list.)


Thank you for your input,
Bill Totman

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

It more than helps: it opens all kinds of possibilities.

So, my question now is:

Should I have designed the database with a 'date' and 'time' columns from the
beginning (vs. just a timestamp)?

Thank you very much,
Bill Totman

On Sunday 29 July 2007 18:26, you wrote:
> 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

On Jul 29, 2007, at 21:12 , Bill Totman wrote:

> Should I have designed the database with a 'date' and 'time'
> columns from the
> beginning (vs. just a timestamp)?

No. You did the right thing from the start.

Michael Glaesemann
grzm seespotcode net