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
|
Список | 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 по дате отправления: