Re: [PHP-DB] Calculating Past Dates In A Different Time Zone

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 whatever time zone the user has selected.
> - The context of this is creating a report
> - An Example: I am want to base the report on records created between 2013-11-01 00:00:00 and 2013-11-07 23:59:59 in the users time zone
>
> The report I am generating is based on date – time in the column “occurance_date”.  This column is type “datetime”.  All dates – times in this column are in GMT time zone.
>
> I want to calculate the starting and ending dates – times using PHP (Since the user selects their time zone based on the available PHP time zone list) and then bind them to the database query:
>
> ===
> SELECT * FROM `journal_entry` WHERE `occurance_date` BETWEEN :starting_date AND :ending_date ORDER BY `occurance_date` ASC
> ===
>
> I normally use the following code to convert between time zones.  But I don’t know how to calculate what time it is in GMT time zone when it is midnight in the users time zone X days ago, or midnight on November 1st 2013 in the users time zone.
>
> ===
> $date = new \DateTime( date('Y-m-d H:i:s') , new \DateTimeZone( 'Canada/Eastern' ));
> $date->setTimezone(new \DateTimeZone( $_SESSION['logged_in_timezone'] ));
>
> $starting_date = $date->format('Y-m-d H:i:s');
> ===
>
> Some reports I am going to be creating will be:
> - last 7 days
> - last 14 days
>
> I also need to be able to do reports which are based on:
> - The month of November 2013
> - The last 3 months
>
> I hope this paints a clear picture of what I am working on.  If you need clarification please ask me.  Thank you for helping me.  Ron
>
>
> Ron Piggott
>
>
>
> www.TheVerseOfTheDay.info
>