Query Timestamp

timestamp

[chrissim]

hi,

My table field contain Timestamp that output something like 1275273026 and i have a hard time trying to query it to shown only today day.
Could someone guide me or show me the right path to query only the current date of today?
Sorry i'm abit lost with this timestamp issue here

   $time = date( 'd-m-Y' ); // Get the current date, in the format of: 12-12-2006
   $timestamp = time();

$today=date("m/d/y",$timestamp);
 $data = mysql_query("SELECT ip FROM uniquelog where timestamp = $today")

[Mahabub]

$today_start_time = strtotime(date("Y-m-d")." 00:00:00");
$today_end_time = strtotime(date("Y-m-d")." 23:59:59");

$data = mysql_query("SELECT `ip` FROM `uniquelog` where `timestamp` >= '".$today_start_time."' AND `timestamp` <= '".$today_end_time."'");

Hope the above solution can help you.

Thanks
Mahabub

[jamie]

use mysql's own date_format options:

SELECT * FROM table WHERE DATE_FORMAT(timestamp, '%d-%m-%Y') = '$today'

[chrissim]

hi Mahabub,

Thanks it works pretty well :)

[chrissim]

hi jamie,

i tried your solution as you suggested but output gave me 0 result.

[Alcoholico]

Using MySQL functions, as suggested by Jamie does not require additional variables declaration and it is not only easier but also more efficient;

Just one php line:

$data = mysql_query("SELECT ip FROM uniquelog WHERE DATE_FORMAT(timestamp, '%d-%m-%Y') = CURDATE()");

However, I'd personally use two lines:

$query = "SELECT ip FROM uniquelog WHERE DATE_FORMAT(timestamp, '%d-%m-%Y') = CURDATE()";
$data = mysql_query($query);

[Matthew1980]

Hi all,

Just to add my 'two cents' to Alcohlico's suggestion, this also means that should you need to debug for any reason, all you need to do is just echo the var after and use the exit; instruction to kill the script so that you can see exactly whats going on.

Also, never hurts to add the ) or die(mysql_error()); to the end of the mysql_ functions so that anything from the DB side can be flagged up, obviously though, once the system is running as you want it, remove the lines, this is purely for security, as you don't want to be giving any information away to potential hackers ;)

Cheers,
MRb