Thread: 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