CODEWITHSUNDEEP
phpmysqldatetimestamp
user3303790
user3303790

Reputation: 137

mysql select date using timestamp

in my database i use a column named startdate and in the column there are rows with timestamps, looking like: 1410178260

Normally, when i use a datetime field and i want to select all the items with the date of today, i run this query:

$sql = "SELECT id FROM agenda2 WHERE DATE(startdate) >= CURRENT_DATE()";

But now, using the timestamps, i don't know how to make a query that selects all the items inserted today. Can someone help me with that?

Upvotes: 0

Views: 117

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to convert to date using the function from_unixtime()

mysql> select FROM_UNIXTIME('1410178260');
+-----------------------------+
| FROM_UNIXTIME('1410178260') |
+-----------------------------+
| 2014-09-08 17:41:00         |
+-----------------------------+

So you may do as

SELECT id FROM agenda2 WHERE DATE(FROM_UNIXTIME(startdate)) >= CURRENT_DATE()

Upvotes: 1

VMai
VMai

Reputation: 10336

If you're using unix timestamps then you've got to use 

$sql = "SELECT id FROM agenda2 WHERE DATE(FROM_UNIXTIME(startdate)) >= CURRENT_DATE()";

Consider that both versions can't make use of an index.

see FROM_UNIXTIME

Upvotes: 0

Related Questions