What kind of data to store "2019-02-06T14:14:38+00:00"?

Asked

Viewed 152 times

1

I am consuming an API that returns me a field date with that data:

2019-02-06T14:14:38+00:00

In my Mysql database I created a field timestamp, because he was the only close I could find.

Well, I can keep in varchar but then I have to keep making conversions when it comes to researching.

I can convert the data when recording and putting in timestamp quiet, it turns out that I use a function that takes the names of the fields dynamically and treat these fields at that time would be a problem since it has several fields this way with different names.

One peculiarity is that this data obtained in the API comes with native English date and time which is converted to our daylight saving time when using in PHP:

date("Y-m-d H:i:s", "2019-02-06T14:14:38+00:00");

In short the doubt is:

There is some type of datetime data that I can use in Mysql that stores the string above and that I don’t need to do the string conversation for date in a select?

php mysql string datetime timestamp
  • 1

    The correct is datetime with timezone, I know that in postgres there is this type. vc can also record this information in a separate field.

  • Yeah, I also use Postgresql, but in this case I’m using Mysql and I didn’t see this option in it. In Mysql we have Datetime and Timestamp types both save the same type, but in the Timestamp description there is an additional "UTC" that I don’t know what it means.

1 answer

3


Before talking about Mysql, a short summary about some concepts. The value 2019-02-06T14:14:38+00:00 contains 3 pieces of information:

Note the letter "T" separating the date and time. This format is defined by the standard ISO 8601.

UTC is the standard from which time zones are defined. The Official Time of Brasilia, for example, has the offset -03:00 (i.e., 3 hours less than UTC). But during daylight saving time, the offset changes to -02:00 (two hours less than UTC). India uses offset +05:30 (5 and a half hours more than UTC) all year. And so on, every time zone in the world is defined as a difference from UTC, as well as having the history of changes in that difference over time (when there is daylight saving or when some country decides to change its time zone).

In the case, +00:00 means "zero hours difference from UTC", which is the same as saying that this date and time "is in UTC".

The offset is important to not leave the date and time ambiguous. For example, if only 2019-02-06T14:14:38 (6 February 2019 at 14:14:38). This date and time occurred at different times in each part of the world. In Australia, 14h on day 6/2 occurred several hours before, compared to the time when the same date and time occurred in Brazil.

Using the offset +00:00, I know the exact moment this occurred. The detail is that now, thanks to offset, 2019-02-06T14:14:38+00:00 (6 February 2019, 14:14:38 in UTC) represents a single moment, which corresponds to a different date and time in each part of the world (for example, it corresponds to day 6/2/19 at 12:14:38 in São Paulo and also to day 7/2/19 at 01:14:38 in Sidnei).

That said, the Mysql date types do not have a decent support - in my opinion - for this type of information:

  • The guy DATETIME supports dates and times (but not offsets) up to the year 9999. If you want to save offset information (+00:00), will have to store in a separate field, because if you only have the date and time, this information will be ambiguous (unless it is "acceptable" to lose information about the offset, or you assume premises like "will always be UTC")
  • The guy TIMESTAMP supports dates and times (but not offsets) until January 19, 2038. And the most confusing point is that, when saving the dates, these are converted from the current Timezone (time zone) to UTC, and when consulting them they are converted from UTC to the current Timezone

By default, the "current Timezone" is the server on which Mysql is running, but a different Timezone can also be set per connection (and this will be used in conversions). Anyway, if you use a Timezone to record the dates and then refer them with a connection that uses another Timezone, the results may be different. If someone defaults the server’s Timezone, the data that was previously recorded will be erroneously converted upon being queried, since the Timezone used for recording is not the same as the one used for searching. This is a very confusing point in my opinion.

Another alternative is to use the value of timestamp - not the Mysql type, but the number of seconds since the Unix Epoch (Unix Epoch is defined as "January 1, 1970, midnight in UTC"). As this value is a number, you can use BIGINT to keep it, for example.

In PHP you can get the value of timestamp using a DateTime:

$d = DateTime::createFromFormat("Y-m-d\TH:i:sP", "2019-02-06T14:14:38+00:00");
echo $d->getTimestamp(); // 1549462478

And to convert this timestamp back to a date:

// criar data a partir do timestamp
$data = new DateTime('@'. 1549462478);

And if you want to convert this date to a specific Timezone, use a DateTimeZone:

$data->setTimeZone(new DateTimeZone('America/Sao_Paulo'));

And if you want to get the current timestamp in Mysql, you can use the function UNIX_TIMESTAMP.

Of course this also implies a conversion of values when making queries, but anyway, you would already need to convert the string received by the service to a date, regardless of the chosen solution.

For more information, see this question, that has many good answers with the pros and cons of each option.

Browser other questions tagged php mysql string datetime timestamp

You are not signed in. Login or sign up in order to post.