fetch rows that matches today timestamp in php mysql

Question

I have table in mysql with three fileds

id
name
time_stamp

here time_stamp stores php time() value.

I want to get the fields that matches todays timestamp ,now i am fetching all rows and check time stamp condition in the loop if it matches will print the current row.

Is there anyway to do this in mysql query itself?

What code have you tried still. Post that so that it will be useful for us to Ensure progress to you. — Naresh Kumar P, Sep 20 '16 at 04:40
Check this http://stackoverflow.com/questions/14769026/mysql-select-where-timestamp-today — Sasikumar, Sep 20 '16 at 04:41
You should go for `SELECT * FROM 'table' WHERE DATE('time_stamp') = NOW()` — Akshay, Sep 20 '16 at 04:47
@BarneyStinson i am using php `time()` to store the time_stamp value — Blessan Kurien, Sep 20 '16 at 04:51
@BlessanKurien Please update your question and add at least one row from your the table. — Rohit Khatri, Sep 20 '16 at 04:56
If you are doing so then above query won't work because it will check perfect time with seconds you can try `SELECT * FROM 'table' WHERE DATE('time_stamp') like concat(CURDATE(),'%')` — Akshay, Sep 20 '16 at 04:56

Kevin · Accepted Answer · 2016-09-20T04:57:24.817

3

Since you insert unix timestamps (time()), you don't need to fetch all rows and make a comparison on the fetching. Just use FROM_UNIXTIME() in MySQL:

SELECT * FROM table_name WHERE FROM_UNIXTIME(`time_stamp`, '%Y-%m-%d') = CURDATE()

Fiddle

edited Sep 20 '16 at 04:57

answered Sep 20 '16 at 04:51

Kevin

41,694
12
53
70

score 1 · Answer 2 · answered Sep 20 '16 at 04:58

You are storing UNIX_TIMESTAMP.

So, there's one way:

SELECT * FROM table_name WHERE FROM_UNIXTIME(time_stamp, '%Y-%m-%d') = CURDATE() _{@Ghost already posted this in his answer}

But one potential problem with this approach is that you cannot make use of index (if any) on time_stamp column.

Here's the other way around:

SELECT * FROM 
table_name
WHERE time_stamp BETWEEN UNIX_TIMESTAMP(CURDATE()) AND (UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY) - 1)

score 0 · Answer 3 · answered Sep 20 '16 at 04:43

0

You can use NOW() or Currentdate() function for that

answered Sep 20 '16 at 04:43

Rockers Technology

477
4
12

score 0 · Answer 4 · answered Sep 20 '16 at 04:52

You can do a between in database to get records from today for example, or you can convert date to the specific format that you need.

to human-readable you can use http://www.w3schools.com/sql/func_date_format.asp

if is the case use between with date_format and select only what you need.

i hope help you

fetch rows that matches today timestamp in php mysql

4 Answers4