change time to EST in php and store in mysql

Question

1. I have a report which will give me the time field as $time='Wed, 25 Aug 2010 13:43:38 +0000'. I am currently storing this into my MySql DB exactly like this and generating a custom report with the same time. I am in EST time so i want to know how i can change the $time field to show it according to 'Wed, 25 Aug 2010 09:43:38 '. i want to remove that +0000 and change the timings to EST. I want to store it into the DB in this format, which i saved as a varchar.

2. I also want to be able to split this $time and save it as $timeday='Wed, 25 Aug 2010' and $timetime='09:43:38'. splitting it by counting the no of characters might work but problem would arise when it is like Wed, 9 Aug 2010 or if i get the report time as Wed, 1 Sept 2010, which i am not sure whether the month would always be 3 characters or can change.

Thank you :)

Answer 1

Well one way (though maybe inefficient, I'm not sure) would be to convert the date to unix timestamp with the strtotime function. Although I'm not sure if it would work in your case. Admittedly I haven't dealt much with dates and times so I haven't used it, myself.

However, if it does work in your scenario, you could then just subtract the correct amount of seconds from the hours (14400 seconds) and then you could use the date function to convert it back correctly and then store it in the database.

You could also use the date function twice to get each result you need in the second scenario.

And of course I haven't tried this, this is just a theory. Anyone more knowledgeable, feel free to correct me and school me.

Answer 2

You can use PHP's DateTime class to do the work for you. You'll need to insert the correct time zone into the following example:

$dt = DateTime::createFromFormat(
    'D, j M Y G:i:s O',
    'Wed, 25 Aug 2010 13:43:38 +0000');

$est = $dt->SetTimeZone(new DateTimeZone('America/New_York'));

echo $est->format('D, j M Y') . "\n";
// Wed, 25 Aug 2010

echo $est->format('H:i:s') . "\n";
// 09:43:38

EDIT

I'd recommend keeping all dates as UTC/GMT, and then changing the timezone as required for display in different locations. Be sure to check out the MySQL Server Time Zone Support docs for information on how MySQL handles and stores dates, as this may affect your decision.

As has already been suggested, you should store your dates and times in the appropriate column type (e.g., DATETIME). You could create a new column of the correct type (shown as new_datetime below), and copy the dates across with something like this:

UPDATE my_table
  SET new_datetime = STR_TO_DATE(old_datetime, '%a, %d %b %Y %H:%i:%s');

That won't change the timezone, however. You could just add/subtract the appropriate number of hours from the original date/time:

UPDATE my_table
  SET new_datetime = DATE_ADD(
    STR_TO_DATE(old_datetime, '%a, %d %b %Y %H:%i:%s'),
    INTERVAL 5 HOUR);

...but you are probably best off leaving them as UTC, and using either the MySQL or PHP timezone functions to handle timezone changes.

Answer 3

First off, never store a datetime as varchar. You just made it completely useless. Always store it in a special datetime format. This way you can do all sorts of things, searching, sorting etc.

When you need to recover your information you can format the output in anyway you want. You can also subtract just the parts you need when requesting from the database.

See: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

To store your data in the correct way.

Use string to time, and then format it into mysql datetime format. Postgresql supports timezone, if I'm correct mysql does not. So you can create a function when requesting the data from the database to display it anyway you need.

Make sure you have set a default timezone in php or else you will get an error.

Store into mysql database You first create correct mysql format: // http://php.net/manual/en/function.strtotime.php

$db_date_timestamp = strtotime('Wed, 25 Aug 2010 13:43:38');
$db_date = date('Y-m-d H:i:s', $db_date_timestamp);

When requesting from database you can use the database to format it in anyway you need. example (mysql):

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009'