Calculating Past Dates In A Different Time Zone

[Ron Piggott]

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

[Matt Pelmear]

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

Post by Ron Piggott
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.
===
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');
===
- last 7 days
- last 14 days
- 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

[Tamara Temple]

Post by Ron Piggott
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.

The time zone offset will always be the same, regardless of what day it is…

Let’s say the user is in US CST, the offset to GMT/UTC is always 6 hours. If the prior date lies within the local DST designation, you can still use that info, in which the offset will be 5 hours. The major glitch happens during the midnight before switching to/from DST, but that’s just a special case.

[Ron Piggott]

A suggestion I was given is to use the mySQL "CONVERT_TZ" command with the
PHP time zone names. But when I do:

SELECT CONVERT_TZ( `journal_entry`.`occurance_date` , 'GMT',
'America/Bahia' ) FROM `journal_entry`

I am receiving "NULL" as the resulting date. Does mySQL accept PHP time
zone names?

Ron

[Matt Pelmear]

Typically, yes.

It is possible you don't have the time zone tables populated.
"America/Bahia" works for me, so I suspect that is the case.
The relevant manual page to load this data (assuming your server is
running in a unix environment) is here:
http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html

If it is running in a Windows environment:
http://dev.mysql.com/downloads/timezones.html


-Matt

Post by Ron Piggott
A suggestion I was given is to use the mySQL "CONVERT_TZ" command with
SELECT CONVERT_TZ( `journal_entry`.`occurance_date` , 'GMT',
'America/Bahia' ) FROM `journal_entry`
I am receiving "NULL" as the resulting date. Does mySQL accept PHP
time zone names?
Ron