CODEWITHSUNDEEP
javascriptphpmysqltimestampmilliseconds
Marvanni
Marvanni

Reputation: 11

Convert MySQL time with milliseconds to javascript timestamp with PHP

I have timestamp from MySQL like: "2016-08-03 11:11:41.04000". This timestamp is the result of a CONCAT('date', ' ', 'time') select.

I need to convert the timestamp with PHP to a numeric only timestamp, so that I can use the timestamp in JavaScript with the milliseconds precision preserved.

Other questions regarding this subject only cover dates without the MySQL milliseconds fragment, or simply suggest strtotime(''datestring) * 1000, which loses the milliseconds precision.

The millisecond precision needs to be preserved so it can be used in JavaScript.

Upvotes: 1

Views: 2109

Answers (4)

Nabi K.A.Z.
Nabi K.A.Z.

Reputation: 10704

Use this simple PHP code for convert date with milliseconds from PHP format to JS timestamp format with keep milliseconds.

Convert 2016-08-03 11:11:41.040 to 1470206501040:

<?php
date_default_timezone_set('UTC');
$date = '2016-08-03 11:11:41.040';
$date = DateTime::createFromFormat('Y-m-d H:i:s.u', $date);
$ts_micro = (float) ($date->getTimestamp() . '.' . $date->format('u'));
$ts_mili = (float) ($ts_micro * 1000);
echo round($ts_mili); //result: 1470206501040

For test, you can reverse convert 1470206501040 to 2016-08-03T11:11:41.040Z time with JS:

var d = new Date(1470222701040);
var n = d.toISOString();
document.write(n); //result: 2016-08-03T11:11:41.040Z

Upvotes: 1

Marvanni
Marvanni

Reputation: 11

I have used both the suggestions of @feeela and @sietse85.

In PHP :

$datetime  = new \DateTime('2016-08-03 11:11:41.04000');
$timestamp = $datetime->format('YmdHisu'); // 2016080311114104000

In javascript :

/**
 * 2016080311114104000
 * @param string yyyymmddhhiissuuuuuu
 */
function dateFromCustomTimestamp(date) {
    date = date.toString();
    return new Date(
        date.substring(0,4),
        date.substring(4,6),
        date.substring(6,8),
        date.substring(8,10),
        date.substring(10,12),
        date.substring(12,14),
        date.substring(14,20)/100
    );
}

Not very nice, but if solves the problem to convert a numeric representation of a timestamp with milliseconds preserved from mysql => php => javascript .

Upvotes: 0

feeela
feeela

Reputation: 29932

You may use a DateTime object. The constructor can consume nearly any date/time format.

$datetime = new DateTime('2016-08-03 11:11:41.04000');
echo $datetime->getTimestamp();
// output: 1470215501

As stated in the comment below, using getTimestamp will lose the milliseconds portion, as a UNIX timestamp is based on seconds and cannot store fractions of a second. You can think of your own timestamp syntax if you need to. For example, output the date & time without any delimiters in between each digit:

$datetime = new DateTime('2016-08-03 11:11:41.04000');
echo $datetime->format('YmdHisu');
// output: 20160803111141040000

On the other hand, JavaScript is able to parse a datetime string including milliseconds directly. So you can also pass the input directly to JS, without transforming it in PHP.

var datetime = new Date('2016-08-03 11:11:41.04000');
console.log(datetime.toJSON());

Upvotes: 2

sietse85
sietse85

Reputation: 1506

I would suggest the following:

Print your concatstring in PHP when using it inside your JS. You could use the JS Date object to set the correct time preserving milliseconds. Here's a reference to the Date object: http://www.w3schools.com/jsref/jsref_obj_date.asp

In short

var d = new Date(year, month, day, hours, minutes, seconds, milliseconds);

Let PHP fill in those parameters (year, month, milliseconds etc) inside your JS

Upvotes: 1

Related Questions