测距日期中的SQL WHERE子句计算

Question

I Have project in which a module needs to display the list of data using a group of specific criteria:

Today
Week
Month
Year

The Database table contains a date field which has datatype as BIGINT(10) and through PHP code we insert time() function value into it. which insert some value like 1311144077,

Now I need to fetch the records from my table as per the labels mentionedabove, how can i do that?

Answer 1

While you're storing datetime in epoch timestamp format, I think solutions using MySQL date/time functions could be very slow. So, I suggest using timestamp itself like below codes.

Usage example

$t = time();

# today
echo date('Y-m-d H:i:s', strtotime('today', $t))."
";
echo date('Y-m-d H:i:s', strtotime('tomorrow', $t))."
";

# this week  
echo date('Y-m-d H:i:s', strtotime('-1 sunday', $t))."
";
echo date('Y-m-d H:i:s', strtotime('sunday', $t))."
";

# this month  
echo date('Y-m-d H:i:s', strtotime(date('Y-m-01 00:00:00', $t)))."
";
echo date('Y-m-d H:i:s', strtotime(date('Y-m-01 00:00:00', strtotime('next month', $t))))."
";

# this year
echo date('Y-m-d H:i:s', strtotime(date('Y-01-01 00:00:00', $t)))."
";
echo date('Y-m-d H:i:s', strtotime(date('Y-01-01 00:00:00', strtotime('next year', $t))))."
";

In query

# this year
$start_of_year = strtotime(date('Y-01-01 00:00:00', $t));
$end_of_year = strtotime(date('Y-01-01 00:00:00', strtotime('next year', $t)));
$query = "SELECT * FROM sometable WHERE somecolumn >= $start_of_year AND somecolumn < $end_of_year";