Better to use two columns or DATETIME

Question

I'm working on a MySQL database which will create a "Today at" list and send it to subscribers. I'm wondering if it's better to use the DATETIME data type on the start and end fields, or two have two columns, startDate and startTime (with the appropriate data types). My first thought was to use DATETIME, but that makes subsequent use of the system a bit awkward, since you can no longer write:

SELECT * FROM event_list WHERE startAt='2009-04-20';

Instead, the best I found was:

SELECT * FROM event_list WHERE startAt LIKE '2009-04-20%';

and I don't like the hack or its potential impact on performance.

Answer 1

Just use the DATE() function.

SELECT * FROM event_list WHERE DATE(startAt) = '2009-04-20'

Answer 2

hi, you can try smf like this

select * from event_list where date(startAt) = '2009-04-20

Answer 3

How about the best of both worlds -- have a table that uses a single datetime column and a view of that table that gives you both date and time fields.

create view vw_event_list
   as select ..., date(startAt) as startDate, time(startAt) as startTime

select * from vw_event_list where startDate = '2009-04-20'

Answer 4

SELECT * FROM event_list WHERE startAt >= '2009-04-20' AND startAt < '2009-04-21'

This will use an index on startAt efficiently and handle the boundary conditions correctly. (Any WHERE clause including a function won't be able to use an index - it has no way to know that the expression result has the same ordering as the column values.

Using two columns is a bit like having columns for the integer and decimal parts of real numbers. If you don't need the time, just don't save it in the first place.

Answer 5

Just one more thing to add: Beware time zones, if you're offering an online service it'll come up sooner or later and it's really difficult to do retroactively.

Daylight Savings Time is especially bad.

(DAMHIK)

Answer 6

The real consideration between separate date and time fields or 1 datetime field is indexing. You do not want to do this: select * from event_list where date(startAt) = '2009-04-20' on a datetime field because it won't use an index. MySQL will convert the startAt data to a date in order to compare it, which means it can't use the index. You want to do this: select * from event_list where startAt BETWEEN '2009-04-20 00:00:00' AND '2009-04-20 23:59:59'

The problem with a datetime field is that you can't really use it a compound index since the value is fairly unique. For example, a compound index on startAt+event isn't going to allow you to search on date+event, only datetime+event. But if you split the data between date and time fields, you can index startDate+event and search on it efficiently.

That's just an example for discussion purposes, you could obviously index on event+startAt instead and it would work. But you may find yourself wanting to search/summarize based on date plus another field. Creating a compound index on that data would make it very efficient.