unix timestamps and php

Question

I have a list of unix timestamps in a database, and I wanting to select the ones that are from today.

i.e If today is Tueday, I want to get all the timestamps that were made today? Is it possible? Is there such a things as strtotime("Today")?

Any help would be great

Answer 1

you can use mktime() to generate the timestamp for the start of the day and then find the database entries with a timestamp greater than that.

Answer 2

Check if DAY(NOW()) and MONTH(NOW()) and YEAR(NOW()) is equal to appropriate value of DAY(timestamp) and MONTH(timestamp) and YEAR(timestamp).

select timestamp from table where DAY(NOW()) = DAY(timestamp) AND MONTH(NOW()) = MONTH(timestamp) AND YEAR(NOW()) = YEAR(timestamp)

Answer 3

You can convert the unix timestamps to sql dates in the SQL using FROM_UNIXTIME(), then compare those to NOW()

SELECT * FROM `tablename` WHERE DATE(FROM_UNIXTIME(`dateFld`)) = DATE(NOW());

Answer 4

If you're using mysql:

SELECT * FROM table WHERE DATE(NOW()) = DATE(FROM_UNIXTIME(timestampcol))

Answer 5

FROM_UNIXTIME(somefield) can be compared to CURDATE() assuming you're using MySQL

SELECT * FROM mytable WHERE FROM_UNIXTIME(datefield,'%Y-%m-%d') = CURDATE();

ETA: Okay, I was assailed by doubt when this answer was marked down. So I went and did a couple of tests. Given MySQL it definitely works. So why the downmod?

Consider this test which outputs 2 identical fields for every row in a table:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()),'%Y-%m-%d')  a , CURDATE() b
  FROM tablewithsomerows 
  WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()),'%Y-%m-%d') = CURDATE();

Answer 6

$start = strtotime(date('Y-m-d 00:00:00')); // Current date, at midnight
$end = strtotime(date('Y-m-d 23:59:59')); // Current date, at 11:59:59 PM

then, you can just select where the timestamp is between the above 2 timestamps:

"SELECT FROM `foo` WHERE `timestamp` BETWEEN '{$start}' and '{$end}'"