CODEWITHSUNDEEP
mysqlsql
Mirgorod
Mirgorod

Reputation: 32673

Unix time to Date in Mysql

i have column visit_time and fields in this column are integers (unix time).

I am trying to make something like this: SELECT * FROM visits WHERE DATE(visit_time) = CURDATE(), but it isn't working...

How to make this code work?

Upvotes: 2

Views: 13457

Answers (5)

cg.
cg.

Reputation: 3678

As an alternative to the other solutions presented here, you could determine the current unix time in MySQL. This might be faster for large data sets:

SELECT * FROM visits WHERE visit_time = UNIX_TIMESTAMP(NOW())

Upvotes: 4

Dor
Dor

Reputation: 7484

SELECT * FROM `visits` WHERE DATE(FROM_UNIXTIME(`visit_time`)) = CURDATE()

See FROM_UNIXTIME(), CURDATE() and DATE()

Upvotes: 3

arnep
arnep

Reputation: 6241

You need to use FROM_UNIXTIME for this:

SELECT * FROM visits WHERE DATE(FROM_UNIXTIME(visit_time)) = CURDATE()

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

SELECT * FROM visits WHERE DATE(FROM_UNIXTIME(visit_time)) = CURDATE()

MySQL Manual :: Date and Time Functions

Upvotes: 2

Shakti Singh
Shakti Singh

Reputation: 86386

SELECT * FROM visits WHERE FROM_UNIXTIME(visit_time, '%Y-%m-%d') = CURDATE()

Upvotes: 10

Related Questions