Search code examples
mysqllaravelunix-timestamp

Mysql: how to save a timestamp UTC without it being altered from mysql timezone?


This question is different from problem to store timestamp into datetime. I have a timestamp, and so no tz info. Also I cannot alter my db structure I am using eloquent, but it worth to say that this problem happens also doing manually a MySQL query insert

What I am doing

My API received this timestamp:

1572864543

It's

Assuming that this timestamp is in seconds:
GMT: Monday 4 November 2019 10:49:03
Your time zone: lunedì 4 novembre 2019 11:49:03 GMT+01:00

When I save on the db, I have NO idea of what is the user's MySQL timezone configuration, but I need that MySQL absolutely do not alters my timestamp.

What I'm sure is that MySQL column is a timestamp

I know that storing a timestamp in Laravel requires a conversion in a sql-like string

I tried to used

Carbon::createFromTimestamp($claims["date"])->toDateTimeString();

Problem

It works, but MySQL gets my string, applies its timezone setting, shit to UTC and the save internally the new timestamp.

So when retrieving

UNIX_TIMESTAMP(received_ts)

I got the wrong result.

Question

I absolutely need to keep my timestamp UNALTERED into the db. How can I save a timestamp without altering it?

Mixed infos

The reason of this is that my product is installed in an unpredictable server timezone; I also cannot force the user to change db timezone to UTC.

Actually my app's timezone is already forced to 'UTC' via laravel config/app.php file

'timezone' => 'UTC',

Solution

  • I resolved forcing utc in the options of my MySQL connection configuration.

    MySQL now do not alters any timestamps