Table Design: Timestamp vs time/date
I'm currently setting up a table to contain user login/logout
information. Just wondered what would be more scalable??better to
index,etc...
Having a 'timestamp' field 'CCYY-MM-DD HH:MM:SS.SS' or two separate
fields one for time 'HH:MM:SS.SS' and one for Date 'CCYY-MM-DD'.
most queries will be of the form:
------------------------------------------------
select ........ from <table> where username='xxx' and date<'CCYY-MM-DD'
and date>'CCYY-MM-DD'
------------------------------------------------
so I was thinking that a separate 'Date only' field would index better..
Any Thoughts??
--
Dale Walker dale@icr.com.au
Dale Walker <dale@icr.com.au> writes:
Having a 'timestamp' field 'CCYY-MM-DD HH:MM:SS.SS' or two separate
fields one for time 'HH:MM:SS.SS' and one for Date 'CCYY-MM-DD'.
Go for the timestamp. Otherwise you'll be cursing yourself the first
time someone wants to know about "all logins between noon Tuesday and
3am Thursday", for example --- easy with timestamps, a pain in the
neck without.
Even when the range boundaries do coincide with midnight, there isn't
likely to be any measurable performance advantage from using a date
column instead of a timestamp column.
Also, type timestamp is Postgres' best-supported date/time type, with
a more complete set of available operations than any of the secondary
date/time types.
regards, tom lane