by month unix timestamp issue

问题:

When i pull a monthly report it's only showing entries from the 2nd day of the month up to the day be before the last of the month. For example a report for month of April only shows:

04/02/12 - 04/29/12

What can i modify to show entries:

04/01/12 - 04/30/12

$month = $_GET['month'];
if ($services =     $db->get_results("SELECT * from vw_newservices where month(from_unixtime(datetime)) = '$month'")) {
foreach ($services as $service) {
$datetime = date("m/d/y",$service->datetime);

Thanks

回答1:

From the manual:

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values.

See here.

If you want to have unix_timestamps without consideration of time zones, you can calculate it yourself.

In MySQL:

From timestamp to unixtime:

SELECT timestampdiff(second, '1970-01-01', yourTimestampColumnOrValue);

The other way

SELECT timestampadd(second, yourTimestampColumnOrValue, '1970-01-01');

For SQL Server see this link.