Ignoring the Time Component in Date Columns
Database systems use Date, Time, and Timestamp
to represent date and time values. While a timestamp type holds a
date and time component, an application may allow the RDBMS to default
the time component. When rows are inserted, updated, or queried, the
application may specify only a date value and leave the RDBMS to extend
the value to include a default time (usually 00:00:00.000).
The challenge with a timestamp is when the application has no immediate interest in the time component. For example, the business question How many orders were taken today? implies all orders taken irrespective of what time the order was booked. If the application defaulted the time component as it stored rows, the query that was used to answer the question returns the count of orders taken today. If the application stored the actual time component, the query likely returns no data, because the number of orders entered at midnight is probably zero.
Relying on the time defaults can be dangerous if the application changes and starts to capture actual times. To avoid this problem, you can
- truncate the time by creating a derived column
- convert the timestamp to a date
- create a hi-low filter