Need to get records from MySQL database by date only from a datetime field

Question

This seems rather simple but it has been giving me a headache. I have a column in my events table that is called 'date_time' and it's type is datetime.

I need to write a query that will get events by day.

Example of table

============================
| id |      date_time      |
============================
| 5  | 2009-03-27 14:16:00 |
============================

Now I need to get the event with the Id = 5, except I only have a date (unix timestamp) to work with. I have tried many things such as converting to mysql format and selecting between 2009-03-27 00:00:00 and 2009-03-28 00:00:00 but I couldn't get it to work.

What is the best way to do this?

Thanks muchly.

Paolo Bergantino · Accepted Answer · 2009-03-27 00:09:49Z

8

SELECT * FROM table
WHERE date_time BETWEEN '2009-03-27 00:00:00' AND '2009-03-27 23:59:59'

Should do it.

Alternatively, try this:

SELECT * FROM table WHERE DATE(date_time) = '2009-03-27'

The DATE() function extracts the date part of the datetime column.

answered Mar 27, 2009 at 0:09

Paolo Bergantino

490k83 gold badges523 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

derobert Over a year ago

The DATE() function approach probably forces a table scan.

alex Over a year ago

I chose your second example. Thanks :)

Luis Melgratti · Accepted Answer · 2009-03-27 00:12:50Z

0

select * from table WHERE date(date_time) = '2009-03-27' works for me

if you have an unix timestamp you could also do

select * from table WHERE UNIX_TIMESTAMP(date_time) = your_timestamp

answered Mar 27, 2009 at 0:12

Luis Melgratti

12.1k4 gold badges35 silver badges32 bronze badges

1 Comment

derobert Over a year ago

Your UNIX_TIMEESTAMP approach won't work, because a UNIX_TIMESTAMP is to the second.

score 0 · Accepted Answer · 2009-03-27 01:18:46Z

0

BETWEEN isn't safe if timestamps include fractional seconds. This is always safe:

SELECT *
FROM table
WHERE date_time >= '2009-03-26 00:00:00' AND date_time < '2009-03-27 00:00:00'

answered Mar 27, 2009 at 1:18

Collectives™ on Stack Overflow

Need to get records from MySQL database by date only from a datetime field

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related