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

Re: timestamp to date and time column migration

From: Bill Totman <totman(at)gmail(dot)com>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: timestamp to date and time column migration
Date: 2007-07-30 02:12:05
Message-ID: 200707292112.12583.totman@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-07-30 03:15:13
Subject: Re: timestamp to date and time column migration
Previous:From: Michael GlaesemannDate: 2007-07-29 23:26:40
Subject: Re: timestamp to date and time column migration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group