Ron,
You could use the same technique here if you want to do the work in PHP:
=== php code ===
assert( convertToGMT('2013-11-01 00:00:00') == '2013-11-01 04:00:00' );
// EST offset by four hours
assert( convertToGMT('2013-11-07 23:59:59') == '2013-11-08 04:59:59' );
// EDT offset by five hours
function convertToGMT( $local_date ) {
$date = new DateTime( $local_date, new
DateTimeZone('America/New_York') );
$date-setTimezone( new DateTimeZone('GMT') );
$gmt_date = $date-format('Y-m-d H:i:s');
return $gmt_date;
}
=== php code ===
For date intervals:
=== php code ===
$date = new DateTime( '2013-11-01 00:00:00', new
DateTimeZone('America/New_York') );
$date-add( new DateInterval('P7D') ); // 7 days
$date-setTimezone( new DateTimeZone('GMT') );
assert( $date-format('Y-m-d H:i:s') == '2013-11-08 05:00:00' );
=== php code ===
Just be careful with mutable vs. immutable DateTime objects ;)
I wouldn't mess with 23:59:59. Instead, use specific comparisons to
make it work:
SELECT * FROM `journal_entry` WHERE `occurrence_date` = 2013-01-01
00:00:00 AND `occurrence_date` 2013-11-08 00:00:00;
...if you're really that concerned about that one second. Alternatively
you could use DateTime::sub() to subtract a single second and still use
BETWEEN.
I would argue that one second doesn't matter for almost any application,
but I obsess over such details myself, so I can't argue that point too
strongly ;)
For reports on a given month or range of months, you can use different
DateInterval values (P1M, etc.), or get the number of days in any
given month from PHP's date() command.
btw, if you were considering doing all of the work in SQL (MySQL), you
could do:
=== sql query ===
SELECT * FROM `journal_entry` WHERE `occurrence_date` BETWEEN
CONVERT_TZ( DATE_SUB(:end_date_in_local_time, INTERVAL 7 DAY),
:local_tz, GMT )
AND
CONVERT_TZ(:end_date_in_local_time, :local_tz, GMT);
=== sql query ===
For the specific problems you called out:
1) Calculating what time it is in GMT when it is midnight in the user's
time zone X days ago:
You just need to use DateTime::sub() to subtract X days
(DateInterval('P'.$X_days.'D')) from midnight today (date('Y-m-d
00:00:00')), then convert the result to GMT. Note that this is midnight
this morning from PHP's perspective if you use date()... my example
below takes into account the user's timezone.
2) Calculating midnight on November 1st 2013 in the user's time zone:
$date = new DateTime( '2013-11-01 00:00:00', new
DateTimeZone($user_tz_str) );
I'll finish with one very specific example for one of the problems you
mentioned.
3) Building a query for the last 3 months.
=== php code ===
$user_tz_str = 'America/New_York';
$tz_user = new DateTimeZone($user_tz_str);
$tz_gmt = new DateTimezone('GMT'); // or UTC, or whatever...
// I wasn't sure which way you meant here, so I did a few.
// I think you'll be able to figure out what you want to do based on one
of these or some variation on them.
$starting_point = 'this morning'; // 'this morning' or 'now' or 'ending
before this month'
if( $starting_point == 'this morning' )
{
// do 3 months back from midnight this morning.
$day_date = new DateTime();
$day_date-setTimezone( $tz_user );
// $day_date-format('Y-m-d').'00:00:00' is midnight this morning
from the perspective of the user's current time
$end_date = new DateTime( $day_date-format('Y-m-d').'00:00:00',
$tz_user );
}
else if( $starting_point == 'ending before this month' )
{
// do three months prior to when this month started.
$day_date = new DateTime();
$day_date-setTimezone( $tz_user );
$end_date = new DateTime( $day_date-format('Y-m').'-01 00:00:00',
$tz_user );
}
else
{
// use now. User timezone doesn't even matter.
$end_date = new DateTime();
}
$start_date = clone $end_date; // clone the object or you'll make a mess
of things.
$start_date-sub( new DateInterval('P3M') ); // subtract 3 months. You
could use whatever DateInterval you want here.
// make sure you do timezone conversion AFTER the DateInterval is
subtracted, if you care about daylight savings time.
$start_date-setTimezone( $tz_gmt );
$end_date-setTimezone( $tz_gmt );
/*
* At this point:
* $start_dt-format('Y-m-d H:i:s') == the beginning of our interval in GMT
* $end_dt-format('Y-m-d H:i:s') == the end of our interval in GMT
*/
// We'll use a PDO prepared statement as an example here. Assume $dbh
comes from somewhere above...
$sth = $dbh-prepare( 'SELECT * FROM `journal_entry` WHERE
`occurrence_date` BETWEEN :start_dt_gmt AND :end_dt_gmt' );
$sth-bindParam(':start_dt_gmt', $start_dt-format('Y-m-d H:i:s'),
PDO::PARAM_STR);
$sth-bindParam(':end_dt_gmt', $end_dt-format('Y-m-d H:i:s'),
PDO::PARAM_STR);
$sth-execute();
=== php code ===
Hope this helps,
-Matt
On 11/10/2013 08:26 AM PT, Ron Piggott wrote:
Hi Everyone
I need help knowing how to calculate midnight “X” days ago in
