Calculating Past Dates In A Different Time Zone

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