Re: 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