Selecting all timestamps for a certain date in PHP

Question

I have a table with a few records and for each of these records I've also added a UNIX_TIMESTAMP. Now, I also have a search engine for those records in which I can choose a date using a jQuery datapicker. My question is how do I make the request so that to select all timestamps from the database for a certain date.

You read a few SQL tutorials, and then try to get it to work by trial and error. Or that was how we learnt stuff back in the olden days, at least. — Sverri M. Olsen, Sep 30 '14 at 20:20

score 2 · Accepted Answer · answered Sep 30 '14 at 20:16

With an index on your timestamp column you will get a faster result with:

SELECT *
  FROM table_name
 WHERE time_stamp_column_name >= :date_picked
   AND time_stamp_column_name <  :date_picked + INTERVAL 1 DAY

Where :date_picked is your bound-in picked date as a string in the format 'YYYY-MM-DD'.

score 1 · Answer 2 · answered Sep 30 '14 at 20:01

1

You can use from_unixtime to convert it into a date

select *
from 
  table
where
date(from_unixtime(your_timestamp_col)) = '2014-10-01'

answered Sep 30 '14 at 20:01

M Khalid Junaid

63,861
10
90
118

1

You could, but you'd render any index useless. – Arth Sep 30 '14 at 20:17

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

use unix timestamp you can also see this

edited May 23 '17 at 11:49

Community

1
1

answered Sep 30 '14 at 20:37

codinglazy

13
4

Selecting all timestamps for a certain date in PHP

3 Answers3