CODEWITHSUNDEEP
phpmysqlsqldatedatetime
kexxcream
kexxcream

Reputation: 5943

Convert unix timestamp to datetime with milliseconds in MySQL

Problem

Converting unix timestamp to datetime while retaining milliseconds.

Background

I am receiving unix timestamp in the following format:

Then I am trying to send it by means of PHP to a column in MySQL that is datetime(3) using TO_TIMESTAMP(). I have also tried FROM_UNIXTIME(), but with the same results.

SQL

$sql = "
  INSERT INTO assoc_table (timestart, timeend) 
  VALUES (TO_TIMESTAMP(:timestart), TO_TIMESTAMP(:timeend))
";

Result

For some reason it does not register the milliseconds.

Desired outcome

Upvotes: 3

Views: 7821

Answers (3)

Bishop
Bishop

Reputation: 131

SELECT FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000)

This will give you the current time as integer in milliseconds.

Upvotes: 0

JoelBonetR
JoelBonetR

Reputation: 1572

Creating a datetime object with php usign the epoch and then formatting it should work: 

$epoch = 1584049707;
$dt = new DateTime("@$epoch");
echo $dt->format('Y-m-d H:i:s.u');

if you need to do the conversion on MySQL check this documentation: https://www.epochconverter.com/programming/mysql

should look like:

DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d-%f');

where -315619200 is your epoch

Upvotes: 0

GMB
GMB

Reputation: 222702

Epoch timestamps represent then number of seconds elapsed since January 1st, 1970; if you want fractional secons, it needs to have a fractional part... which is not the case with the input that you are given to MySQL; this is then reflected in the results that you are getting.

Given an epoch timestamp with a fractional part, from_unixtime() works as expected:

select from_unixtime(1584049707.123)

Returns:

2020-03-12 21:48:27.123

Note: datetime(3) is the relevant format to store such value.

Upvotes: 6

Related Questions