Convert timestamp coming from SQL database to String

Question

I am saving the timestamp in SQL as bigint(20). The number is correct and in android or https://www.epochconverter.com it works fine.

However I am not able to create a date-string based on the timestamp received from database.

First of all, the timestamp seems to come from database as a String, so I can't just say echo date("d.m.Y, $timestamp). The result is the famous 31.12.1969.

So I tried echo date("d.m.Y, strtotime($timestamp)). However, even though strtotime is said to be able to convert almost everything to a timestamp, a simple String containing a timestamp is not possible. Results still remained on the last day of Brian Adams probably favorite year.

Some progress I made by casting the $timestamp to a float value like so: echo date("d.m.Y", floatval($timestamp));. However, now things got really confusing for me. I seemed to have successfully converted my timestamp, however, date() gave me the dates around 02.09.52299.

The timestamps I am using are timestamps of current time, e.g. 1588489252657, which currently leads to the date 23.03.52307.

So all I want is to come to a date based on the timestamp 1588489252657 to see the 03.05.2020 on my screen.

Thanks for any advice!

Convert that timestamp from milliseconds to seconds and you will get the right date — John Conde
– John Conde, Commented May 4, 2020 at 18:05
From your own conversor: Assuming that this timestamp is in milliseconds — Álvaro González
– Álvaro González, Commented May 4, 2020 at 18:06
Thanks for the two answers. The main point for me was that I had to transform the timestamp from milliseconds to seconds. After this using echo date("d.m.Y", floatval($timestamp) / 1000); worked fine — Gerke
– Gerke, Commented May 5, 2020 at 3:35

Harish ST · Accepted Answer · 2020-05-04 18:20:31Z

3

<?php

$timestamp = 1588489252657; // Timestamp in Milliseconds
$seconds = round($timestamp/1000, 0); // Timestamp Rounded to Seconds

$date = new DateTime();  // Create PHP DateTime Object
$date->setTimestamp($seconds); // Set the Timestamp
echo $date->format('Y-m-d H:i:s'); // Specify the Required Format

The answers are pretty much in the comment sections. But I have shared this answer since this is another approach in OOP fashion. You can leverage the power of PHP's DateTime Class.

PHP Official Documentation For DateTime Class Link Below: PHP DateTime Class

answered May 4, 2020 at 18:20

Harish ST

1,51110 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerke Over a year ago

Thanks for the two answers. The main point for me was that I had to transform the timestamp from milliseconds to seconds. After this using echo date("d.m.Y", floatval($timestamp) / 1000); worked fine

JesusValera · Accepted Answer · 2020-05-04 18:38:06Z

2

You have to transform the timestamp to seconds first.

$timestamp = 1588489252657;
$dateInUnixSeconds = round($timestamp / 1000, 0);
$date = \DateTimeImmutable::createFromFormat('U', (string) $dateInUnixSeconds);

echo $date->format('d.m.Y');

PS: I recommend you to use the \DateTimeImmutable object to avoid mutability problems. https://github.com/Chemaclass/php-best-practices/blob/master/technical-skills/immutability.md

answered May 4, 2020 at 18:38

JesusValera

6576 silver badges15 bronze badges

1 Comment

Gerke Over a year ago

Thanks for the two answers. The main point for me was that I had to transform the timestamp from milliseconds to seconds. After this using echo date("d.m.Y", floatval($timestamp) / 1000); worked fine.

Collectives™ on Stack Overflow

Convert timestamp coming from SQL database to String

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related