CODEWITHSUNDEEP
phpmysql
sineverba
sineverba

Reputation: 5172

PHP: right way to manage DateTime (timestamp Y2038 Bug aware!)

In my "tool box" i'm using this function:

function dataAttuale() {
    $now                    = new DateTime();
    $dataAttuale            = $now->format(DateTime::ISO8601);
    $offset                 = $now->getOffset();
    date_default_timezone_set('UTC');
    $nowUTC             = new DateTime();
    $dataUTC                = $nowUTC->format(DateTime::ISO8601);
    $orario             = array();
    $orario['dataAttuale']  = $dataAttuale;
    $orario['dataUTC']      = $dataUTC;
    $orario['offset']       = $offset;
    return $orario;
}

I get this array

Array
(
    [dataAttuale] => 2013-10-18T11:03:52+0200
    [dataUTC] => 2013-10-18T09:03:52+0000
    [offset] => 7200
)

So i could save in a datetime MySql field a datetime referred to UTC.

Now, i've some trouble about this.

1) I would save also offset (in seconds). What's best Mysql field? I think max seconds can be +14hour * 60 * 60 = 50400 and -12hours*60*60 = -43200

2) Do you think is notable save also offset? I.e., for example, several API services return a date in UTC + offset...

Thank you very much!

UPDATE:

Thank you to both people. Now i'm saving in MySQL datetime in UTC format and varchar timezone. With a couple of code I'm getting what I want:

$orario = new DateTime($value['creazione'], new DateTimeZone($value['timezone']));
$orario     = $orario->format(DateTime::ISO8601);

The output is (for Europe/Rome)

2013-10-19T09:27:54+0200

And for America/Montreal

2013-10-19T09:29:16-0400

And for Australia/Melbourne

2013-10-19T09:30:31+1100

(difference of minutes//seconds it the time to change in my PHP scripts the default Timezone).

Now I think that:

1) I can laugh about Y2038 bug, abandoning (sigh :( ) timestamp :(

2) I can safely travel around the world and use my own Calendar (naaaa... i'll use forever Google Calendar, of course)

Upvotes: 0

Views: 325

Answers (2)

t.niese
t.niese

Reputation: 40872

MySQL is award of timezones (it does not store the timezone with the date, but it converts it to a normalized format), so most of the time you do not need to have an additional field with the offset.

You just need to make sure that you set the correct time_zone for your connection.

So if you have a date and you want to store it in your database you have different possibilities:

  1. You can use SET time_zone = timezone; for your connection. Way you tell MySQL that the date you send or receive from MySQL should be in the give timezone. MySQL will internally convert it to a normalized format.

  2. If you want to insert dates that have different timezones then set for the time_zone then you could use CONVERT_TZ(dt,from_tz,to_tz). from_tz is the timezone of your date, to_tz the one that is set for your connection.

There are for sure situations where the timezone could matter. If that is true for your case is not exactly clear out of your question.

Upvotes: 1

deceze
deceze

Reputation: 522480

It doesn't make a lot of sense to save the offset. There are two possible values you can be interested in with a timestamp:

  1. the general global timestamp, e.g. "the point in time in this world at which it was 12:52am on Sept. 6 2013 UTC"
  2. the specific local time of some point in time, e.g. "17:34 on Dec. 19th 2012 in Manila, Philippines"

Notice that both of these are actually the same thing, they express a point in time in the notation of wall clock time and date at a specific location or timezone. The only difference is that UTC is a specified standard "location" relative to which other timezone offsets are expressed; but there's no reason Manila in the Philippines couldn't be used for the same purpose.

So when you want to store an absolute timestamp, you either:

  1. decide that all your times are stored in a specific timezone like UTC and simply store that timestamp
  2. decide that you are interested in a specific local time and store the timestamp and its timezone

Either way you need the timestamp and you need to know which timezone it's in. In 1. you decide in advance that all timestamps are in the same defined timezone and don't need to store it, in 2. you explicitly save that timezone information.

An offset is not a good thing to store, because it varies throughout the year. The offset in summer may be +6 hours to UTC, but in winter may be +7. If you need to do date calculations on a localized time later on, an offset is misleading and doesn't help you much. If you know the timezone you're talking about, you can get the offset for any time of the year later on.

MySQL doesn't support a DATETIME + TIMEZONE field (Postgres for example does), so you need to store the timezone (e.g. "Europe/Berlin") in a separate text field. If you don't need to associate a timestamp with a specific location at all, then there's no need for a timezone and you just need to store the normalized timestamp, e.g. normalized to UTC.

Upvotes: 1

Related Questions