1

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',
realtebo
  • 23,922
  • 37
  • 112
  • 189
  • are you using eloquent ? – pinoyCoder Nov 04 '19 at 11:52
  • 2
    Can you change the column type? In that case changing it from TIMESTAMP to DATETIME would solve the problem. Per the doc: `MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)` – thephper Nov 04 '19 at 12:21
  • Why not store the timestamp as an integer then? Surely MySQL or your application won't change a raw numeric value – Nico Haase Nov 05 '19 at 12:51

1 Answers1

0

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

MySQL now do not alters any timestamps

realtebo
  • 23,922
  • 37
  • 112
  • 189