Intermittent JSON "time" problem with PHP strtotime(...)

Application Development
1

I am in the process of trying to monitor and chart temperature sensors located throughout UK and soon to be another in Bangkok.

Site Demo: https://supiet.tk/

Currently the TTN JSON data:

  1. sent to a dedicated server
  2. data curled, parsed and uploaded to a MySql database
  3. charts created for each sensor

Problem with Intermittent JSON “time”

I have created a function to parse the JSON “time” value, extract the datetime informaton and ignore the mSecs/Seconds, etc. I would prefer to use the far simpler PHP strtotime(…) function…

<?php 
...
...
  $iFail = $iPass = 0;

  while($row = $oRows->fetch_object() ):  

      $timestamp = strtotime( $row->time );

      if( $timestamp ):
        $iPass++;
        if($iPass < 11):
          echo '<br> PASS: Device_id = ' .$row->dev_id .' --- TIME = '  .$row->time;
          echo $iPass===10 ? '<br>' : NULL;
        endif;  
      else:
        $iFail++;
        if($iFail < 11):
          echo '<br> FAIL: Device_id = ' .$row->dev_id .' --- TIME = '  .$row->time;
          echo $iFail===10 ? '<br>' : NULL;
        endif;  
      endif;  

  endwhile;  
  echo '<br> $iPass ==> ', $iPass;
  echo '<br> $iFail ==> ', $iFail;
  echo '<br> $Total ==> ', $iPass + $iFail;

Output:

FAIL: Device_id = 3 --- TIME = 2018-11-10T16:00:37.693331725Z
FAIL: Device_id = 1 --- TIME = 2018-11-10T16:06:14.769221807Z
FAIL: Device_id = 1 --- TIME = 2018-11-10T16:21:43.884132598Z
FAIL: Device_id = 3 --- TIME = 2018-11-10T16:22:59.713155252Z
FAIL: Device_id = 2 --- TIME = 2018-11-10T16:26:17.988400055Z
FAIL: Device_id = 1 --- TIME = 2018-11-10T16:29:28.364164087Z
FAIL: Device_id = 1 --- TIME = 2018-11-10T16:37:12.865353714Z
FAIL: Device_id = 3 --- TIME = 2018-11-10T16:42:52.600596748Z
FAIL: Device_id = 1 --- TIME = 2018-11-10T16:44:57.547324099Z
FAIL: Device_id = 2 --- TIME = 2018-11-10T16:46:41.122911915Z

PASS: Device_id = 1 --- TIME = 2018-10-15T19:51:59.11664968Z
PASS: Device_id = 1 --- TIME = 2018-10-15T20:04:21.82335334Z
PASS: Device_id = 1 --- TIME = 2018-10-15T20:19:12.78846381Z
PASS: Device_id = 2 --- TIME = 2018-10-15T20:20:29.09019818Z
PASS: Device_id = 2 --- TIME = 2018-10-15T20:27:55.28019383Z
PASS: Device_id = 1 --- TIME = 2018-10-15T21:16:08.66904935Z
PASS: Device_id = 2 --- TIME = 2018-10-15T21:59:39.71886252Z
PASS: Device_id = 2 --- TIME = 2018-10-15T22:14:32.27015227Z
PASS: Device_id = 2 --- TIME = 2018-10-15T22:19:29.90908701Z
PASS: Device_id = 1 --- TIME = 2018-10-15T22:30:24.01548308Z

$iPass ==> 1896
$iFail ==> 17253
$Total ==> 19149

I would be grateful for information on why the JSON “time” is not consistent, how to debug, isolate and correct the problem.

2

To ask what seems to be obvious: so all times with more than 8 digits in the decimal part of the seconds cause it to fail?

As far as I know, ISO 8601 does not specify a maximum number of decimals, so I’m tempted to say that’s a PHP bug, not a bug in the JSON output.

If stored as the ISO 8601 string that the JSON gives you, then I guess the following would do the trick. (That should also work if the decimal part is shorter than 8 digits, I think.)

$timestamp = strtotime( substr($row->time, 0, 28) . 'Z' );

But maybe it’s stored in your database differently?

2 Likes
3

@ arjanvanb Many thanks for your prompt reply.

I noticed the additional numeric character in the trailing milliseconds and had to truncate the milliseconds to make the date MySqli friendly.

Further investigation revealed the following post which appears to pinpoint the problem to Golang. I can appreciate why Golang is used because it excels in handling large numbers of simultaneous HTTP Requests. It is a pity Golang sometimes returns an incorrect JSON “time” format :frowning:

It appears that PHP rejects incorrect formatted datetimes and returns a negative boolean result.

PHP: DateTime::createFromFormat - Manual

The Workaround

I used the following PHP script because the trailing milliseconds are not required:

  // $row->time = '2018-10-27T08:36:25.356781773Z';	 

  $json_datetime = strstr($row->time, '.', true); // remove milliseconds
  $datetime      = new DateTimeImmutable($json_datetime );

  // Result:
  /*
	$json_datetime ==> 2018-10-27T08:36:25

    DateTimeImmutable Object
    (
        [date] => 2018-10-27 08:36:25.000000
        [timezone_type] => 3
        [timezone] => Asia/Bangkok
    )
  */
2 Likes
4

Just as an aside, as for why the string is sometimes shorter: I assume it’s due to leaving out trailing zeroes. Your:

…shows that about 10% fails, and when evenly distributed then about 1/10th of the times would indeed have a trailing zero. So I guess you might also see strings that are even shorter, as 1/100th of the values will have 2 trailing zeroes, and so on.

I still don’t think it’s invalid. It’s not per ISO 8601.

1 Like