Sorting through a Database query for all rows created in the same day Unix Timestamp in PHP

Question

Hi,

I am developing a PHP application which will handling many company articles.

Now I am creating a page which should order articles BY DATE ie all articles created in a certain day are shown with the appropriate heading and so on for all the articles.

I have used Unix timestamps to save the dates in the MySql db but I cant find code which only sorts dates by days. Can I please get some assistance.

Thanx

Answer 1

You can use mktime() to get the timestamps you would want to use to bin the entries:

http://us.php.net/manual/en/function.mktime.php

$timestamp_for_my_birthday = mktime(0,0,0,10,16,1984);
$timestamp_for_the_next_day = mktime(0,0,0,10,17,1984);

if($time > $timestamp_for_my_birthday && $time < $timestamp_for_the_next_day){
    // Time is on my birthday
}

To make this into a MySQL query:

$SQL = "SELECT * FROM dates WHERE date > $timestamp_for_my_birthday AND date < $timestamp_for_the_next_day;";

To order by date I think it would go something like:

SELECT * FROM dates ORDER BY FROM_UNIXTIME(time, '%M %d %Y');

Answer 2

You can order by the timestamp column, but if you want a string representation of which day the record belongs to, you can get any part of the date with the FROM_UNIXTIME(timestamp, 'format') function.

select FROM_UNIXTIME(timestampColumn, '%Y %m %d')

The format values in the second parameter can be adjusted based on the table here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Answer 3

Can you do a

SELECT DATE(FROM_UNIXTIME(my_field_name)) FROM table_x ORDER BY my_field_name