[RESOLVED] Dates and Time Zones?

[h4x0rmx]

I have a db with 6+ int & text fields to store 2 dates.
I'm suggesting to my client to have only 2 DATE/DATETIME fields instead of the 6+.

My idea was to store in the first field one date in the original time zone (I need to be able to know the original time zone) and in the other the same date in the EDT time zone.
The thing tis that both dates are the same, except that they are in different time zones.
I was wondering if it'd be better to store the date/time just once and somehow know the original time zone and then when querying the db get the same time in EDT?

Is is possible?
Any other ideas?
I'll appreciate your help!

[johanafm]

All you need is one datetime field where you store time in one preselected timezone, and one field to store user timezone. Knowing the date/time and both timezones you can convert to datetime in the other timezone.

[h4x0rmx]

johanafm;10977601 wrote:

All you need is one datetime field where you store time in one preselected timezone, and one field to store user timezone. Knowing the date/time and both timezones you can convert to datetime in the other timezone.

Good enough! 🙂
Any examples of how converting between timezones? Let's say I have a date in PDT and want it in EDT?

[johanafm]

You can easily convert times between timezones using DateTime and DateTimeZone, assuming you create some kind of mapping from your three letter acronyns to timezones values acceptable by DateTimeZone. I'm not really sure about EDT and PDT, so I'm merely guessing that America/New_York and Pacific/Honolulu would be acceptable values. See [man]timezones[/man] for possible values.

$timezones = array('EDT' => 'America/New_York', 'PDT' => 'Pacific/Honolulu');
# datetime in the timezone PDT
$dt = '2011-04-07 13:15:00';
$from_tz = 'PDT';
$to_tz = 'EDT';

$date = new DateTime($dt, new DateTimeZone($timezones[$from_tz]));

echo '<h3>PDT</h3>';
echo $date->format('H:i') . '<br/>';

$date->setTimezone(new DateTimeZone($timezones[$to_tz]));
echo '<h3>EDT</h3>';
echo $date->format('H:i') . '<br/>';

Or, you could store the accepted tz values from the beginning, i.e. America/... rather than EDT and Pacific/... rather than PDT, where ... is some acceptable city.

[h4x0rmx]

Sounds good, I think it's pretty straight forward to do it in PHP, but is it possible to do the conversion right in the SQL script?
I know I always want the original date and the same date in EDT.
Any ideas?

[bradgrafelman]

If you know the difference in the # of hours, just add "+ INTERVAL $X HOUR" (where $X is the number of hours, and where the '+' can be changed to a '-' as needed).

[h4x0rmx]

Thanks everybody for your input.
This is what I ended up doing. I saved the original datetime and the GMT offset separately.
My query looks like this:

SELECT cl.metropolitan_area, cl.date_time, edt.date_timeEDT
  , DAYOFMONTH(cl.date_time) dayOrg
  , MONTH(cl.date_time) AS monthOrg
  , DATE_FORMAT(cl.date_time, '%h:%i %p') AS dateTimeOrg
  , DAYOFMONTH(edt.date_timeEDT) dayEDT
  , MONTH(edt.date_timeEDT) AS monthEDT
  , DATE_FORMAT(edt.date_timeEDT, '%h:%i %p') AS dateTimeEDT
  FROM(
    SELECT CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') AS date_timeEDT, id
      FROM ccrraa
    ) edt
  INNER JOIN ccrraa AS cl ON cl.id = edt.id
ORDER BY cl.gmt_offset DESC;

By the way, is this query more efficient than the following one?
I would think it is, but you never know.
(The following query calls CONVERT_TZ() 3+ times, while the other one has a subquery and an inner join)

SELECT metropolitan_area, date_time, gmt_offset, gmt_offset + 4 AS hours, CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') AS EDT
  , DAYOFMONTH(date_time) AS day, MONTH(date_time) AS month, DATE_FORMAT(date_time, '%h:%i %p') AS date_time_
  , DAYOFMONTH( CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') ) AS dayEDT
  , MONTH( CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') ) AS monthEDT
  , DATE_FORMAT( CONVERT_TZ(date_time, CONCAT(gmt_offset, ':00'), '-4:00') , '%h:%i %p') AS date_timeEDT
  FROM ccrraa
  ORDER BY gmt_offset DESC;

[johanafm]

Just check how long they take to execute. The loop is used in case they are fairly equal in speed, in which case you need to execute the queries multiple times to get a noticable time difference.

Also, if you have little data in your tables you may not get the same results as if you have a lot of data in your tables. In case you expect data to grow over time, you really should fill your tables with dummy data, say a million rows per table or whatever order of magnitude you expect the data to end up in eventually.
If you on the other thand will be dealing with small data sets, then keep the amount of rows low.

$q1 = 'First query';
$q2 = 'Second query';

$stmt = $db->prepare($q1);
$s = microtime(true);
for ($i = 0; $i < 100; ++$i)
{
	$stmt->execute($q1);
}
printf('Query 1 execution time: &#37;f', microtime(true) - $s);

$stmt = $db->prepare($q2);
$s = microtime(true);
for ($i = 0; $i < 100; ++$i)
{
	$stmt->execute($q1);
}
printf('Query 2 execution time: %f', microtime(true) - $s);