0

I'm currently receiving the following date object from JS inside PHP via a POST request: 

Tue Feb 25 2020 22:31:03 GMT+0100 (Mitteleuropäische Normalzeit)

How can I format this now to the SQL timestamp format Y-m-d H:i:s including the given timezone at the end?

I've tried this: 

date( 'Y-m-d H:i:s', 'Tue Feb 25 2020 22:31:03 GMT+0100 (Mitteleuropäische Normalzeit)' )

But this causes an error:

date() expects parameter 2 to be int, string given

I've already searched on SO but I can only find questions about timestamps.

ADyson
  • 57,178
  • 14
  • 51
  • 63
Mr. Jo
  • 4,946
  • 6
  • 41
  • 100
  • 1
    The first code line looks like `toString()` output of a Date object. Which means you can use `getTime()` to turn it into seconds. These can be passed into `time` in `int` format, i.e. `intval($_POST['seconds'])` or what have you. –  Feb 25 '20 at 21:41
  • @ChrisG Do you mean getTime() inside JS and then format it how I want it? If yes, this turns out in PHP when passing `getTime()` from JS in this code `date( 'Y-m-d H:i:s', intval( $created_at ) )`: `52122-09-21 23:28:27` ($created_at is my unchanged value from the request) – Mr. Jo Feb 25 '20 at 21:43
  • 1
    `new Date().getTime()` returns a huge integer, the number of seconds since 1970-01-01 00:00:00 UTC. edit: the number of milliseconds apparently, so divide by 1000 first. –  Feb 25 '20 at 21:45
  • 1
    I've found something about the timestamp. It's different between JS and PHP. So I did this `date( 'Y-m-d H:i:s', $created_at / 1000 )` Output: `2020-02-25 21:47:52` Problem: The timezone is not current. How can I respect the timezone given by the browser now? – Mr. Jo Feb 25 '20 at 21:53
  • 1
    You don't. The time you see is "one hour behind" because it's UTC. Creating a `Date()` from it in the browser and displaying it will automatically use local time again. Never, ever, store timestamps in local time. Always use UTC, that way it's universal. –  Feb 25 '20 at 21:55
  • But when I set my field in my DB to `CURRENT_TIMESTAMP` as default and create a new entry, the local timezone is used: `2020-02-25 22:57:39`. In PHP it's `2020-02-25 21:57:39`. So this is the actual problem for me. And by the way I can't set the timestamp by default because there is a time gap between the browser and the request / saving. So I need to take the exact browser timestamp. – Mr. Jo Feb 25 '20 at 21:59
  • Sounds like you've answered your own question. If you always need to use browser time, you can't use `CURRENT_TIMESTAMP` anyway, right? So always pass the seconds over and store those. –  Feb 25 '20 at 22:11
  • Also see here: https://javorszky.co.uk/2016/06/06/today-i-learned-about-mysql-and-timezones/ –  Feb 25 '20 at 22:18

1 Answers1

0

strtotime (https://www.php.net/manual/en/function.strtotime.php) will take a string and give you what date needs. Try:

date( 'Y-m-d H:i:s', strtotime('Tue Feb 25 2020 22:31:03 GMT+0100 (Mitteleuropäische Normalzeit)') );
JorgeObregon
  • 3,020
  • 1
  • 12
  • 12
  • I'm downvoting this because turning a Date object into a textual representation only to parse it back into a date object is obviously bad practice. –  Feb 25 '20 at 21:42
  • This turns out: 1970-01-01 00:00:00 – Mr. Jo Feb 25 '20 at 21:42
  • @ChrisG, I agree, it's bad practice, but it's "technically" the answer to the question :) A better approach would be to convert the date to time in JS https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getTime and use that in PHP. That will also save some bits in network payload size. – JorgeObregon Feb 25 '20 at 21:47
  • Fair enough, but the answer to an xy problem should never ignore the fact that it's an xy problem. Other people might find this question, and the bad practice propagates. Just like with w3schools. –  Feb 25 '20 at 21:50
  • Totally agree, kudos!! @Chris – JorgeObregon Feb 25 '20 at 21:51