date&time vs timestamp question

Поиск
Список
Период
Сортировка
От Ron Mayer
Тема date&time vs timestamp question
Дата
Msg-id POEDIPIPKGJJLDNIEMBEIEBLCKAA.ron@intervideo.com
обсуждение исходный текст
Ответ на Re: postgresql-7.3 implementation  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: date&time vs timestamp question  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-novice
I've been asked what the best way for storing dates× in a postgresql database.

Anyone have a summary of the advantages/disadvantages of
   create table a (dat date, tim time, ...);
vs.
   create table b (dattim timestamp, ...);

The table will be kinda large, perhaps 10 million rows, and
many queries would be searching creating aggregate information
of events that happened on a particular day.  Perhaps
  select min(price),thing from table where dat='2002-01-01' group by thing;
will be a typical query.  Sometimes but rarely will aggregate queries want
sub-day resolution, but I need the time because the total order is important.


In my mind..

  Advantages for a separate date, time:
       * somewhat nicer query syntax...
           select whatever from a where dat='2003-01-01';
         vs
           select whatever from a where dattim>='2003-01-01' and dattim<'2003-01-02;
         or
           select whatever from a where date_trunc('day',dattim)='2003-01-01'

       * Could analyze generate have meaningful statistics
         with a separate date column for the optimizer?  For example, the
         most-common values in pg_stats is probably less useful for timestamps
         than dates.

  Advantages for a combined timestamp:
       * looks like a few bytes smaller.
       * slightly easier to isolate a specific event based on time.

Any other advantages/disadvantages/opinions welcome.

   Ron


В списке pgsql-novice по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: ERROR: XLogFlush:
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: date&time vs timestamp question