CODEWITHSUNDEEP
mysql
abhinsit
abhinsit

Reputation: 3272

Datetime vs Date and Time Mysql

I generally use datetime field to store created_time updated time of data within an application.

But now i have come across a database table where they have kept date and time separate fields in table.

  1. So what are the schema in which two of these should be used and why?
  2. What are pros and cons attached with using of two?

Upvotes: 25

Views: 26580

Answers (4)

mikiqex
mikiqex

Reputation: 5323

One difference I found is using BETWEEN for dates with non-zero time.

Imagine a search with "between dates" filter. Standard user's expectation is it will return records from the end day as well, so using DATETIME you have to always add an extra day for the BETWEEN to work as expected, while using DATE you only pass what user entered, with no extra logic needed.

So query

SELECT * FROM mytable WHERE mydate BETWEEN '2020-06-24' AND '2020-06-25'

will return a record for 2020-06-25 16:30:00, while query:

SELECT * FROM mytable WHERE mydatetime BETWEEN '2020-06-24' AND '2020-06-25'

won't - you'd have to add an extra day:

SELECT * FROM mytable WHERE mydatetime BETWEEN '2020-06-24' AND '2020-06-26'

But as victor diaz mentioned, doing datetime calculations with date+time would be a super inefficient nightmare and far worse, than just adding a day to the second datetime. Therefore I'd only use DATE if the time is irrelevant, or as a "cache" for speeding queries up for date lookups (see Elwin's answer).

Upvotes: 0

Elwin
Elwin

Reputation: 791

There is a huge difference in performance when using DATE field above DATETIME field. I have a table with more then 4.000.000 records and for testing purposes I added 2 fields with both their own index. One using DATETIME and the other field using DATE.

I disabled MySQL query cache to be able to test properly and looped over the same query for 1000x:

SELECT * FROM `logs` WHERE `dt` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10; DATETIME INDEX: 197.564 seconds.

SELECT * FROM `logs` WHERE `d` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10; DATE INDEX: 107.577 seconds.

Using a date indexed field has a performance improvement of: 45.55%!!

So I would say if you are expecting a lot of data in your table please consider in separating the date from the time with their own index.

Upvotes: 40

victor diaz
victor diaz

Reputation: 9

The tricky part is when you have to do date arithmetic on a time value and you do not want a date portion coming into the mix. Ex:

myapptdate = 2014-01-02 09:00:00

Select such and such where myapptdate between 2014-01-02 07:00:00 and 2014-01-02 13:00:00

1900-01-02 07:00:00 2014-01-02 07:00:00

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271061

I tend to think there are basically no advantages to storing the date and time in separate fields. MySQL offers very convenient functions for extracting the date and time parts of a datetime value.

Okay. There can be some efficiency reasons. In MySQL, you can put separate indexes on the fields. So, if you want to search for particular times, for instance, then a query that counts by hours of the day (for instance) can use an index on the time field. An index on a datetime field would not be used in this case. A separate date field might make it easier to write a query that will use the date index, but, strictly speaking, a datetime should also work.

The one time where I've seen dates and times stored separately is in a trading system. In this case, the trade has a valuation date. The valuation time is something like "NY Open" or "London Close" -- this is not a real time value. It is a description of the time of day used for valuation.

Upvotes: 28

Related Questions