Ok I have a webhook which posts data to the a php script. My php script is inserting the data into a SQL table. All the data is inserting fine except the {PARTICLE_PUBLISHED_AT} value. I am trying to directly insert the {PARTICLE_PUBLISHED_AT} value into a datetime field in the database but it fails. If I substitute the timestamp with a string constant the record will insert correctly so I know it has to do with the format conversion to the SQL datetime field.

I am no SQL expert so any pointers would be appreciated.

First, what type of SQL Server?

Also, can you post code Snippets of the PHP SQL insert, and then the SQL script where the variables are declared?

Here is the php sql.

$sql = "INSERT INTO `siteData` (`flr`, `fd` ,`dir`,`inS`,`devId`, `ts`) VALUES ('$_POST[FLR]','$_POST[FD]' ,  '$_POST[DIR]' , '$_POST[InS]' ,'$_POST[id]', '$_POST[ts]'  )";

if (mysqli_query($link, $sql)) {
    echo "New record created";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);

Here is the SQL:

mysql> describe siteData;
| Field | Type     | Null | Key | Default | Extra          |
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| flr   | int(11)  | YES  |     | NULL    |                |
| fd    | int(11)  | YES  |     | NULL    |                |
| dir   | int(11)  | YES  |     | NULL    |                |
| inS   | int(11)  | YES  |     | NULL    |                |
| devId | char(24) | YES  |     | NULL    |                |
| ts    | datetime | YES  |     | NULL    |                |
7 rows in set (0.00 sec)

mysql> select version();
| version()               |
| 5.7.23-0ubuntu0.18.04.1 |
1 row in set (0.01 sec)

And here is the hook response:

"Error: INSERT INTO `siteData` (`flr`, `fd` ,`dir`,`inS`,`devId`,                                     `ts`) VALUES ('0','1' ,  '0' , '0' ,'2e00xxxxxxxxxxxxxxxxxxxxxx', '2018-10-10T20:5                                    3:36.207Z'  )<br>","ttl":60,"published_at":"2018-10-10T20:53:36.426Z","coreid":"                                    particle-internal"}

In the Hook response error, there is a very large gap of white space in the middle of the timestamp. Is that real? Or is that like a copy and paste error to the Forum? If it’s real can you put in a Serial print statement that shows you the data before it is published. Then you can look at the serial monitor to validate that isn’t real.

That was just a pasting error. Sorry.

INSERT INTO siteData (flr, fd ,dir,inS,devId, ts) VALUES (‘0’,‘1’ , ‘0’ , ‘0’, ‘2e0046xxxxxxxxxxxxxxxxxxx’, ‘2018-10-10T20:53:36.207Z’ )

Okay, great. I think the extra characters T & Z in that timestamp are throwing off MySQL. See this link for additional information.

Based on that discussion, I think the PHP statement should be something like this. I’m no good at PHP so try and let us know if it works…:

$sql = "INSERT INTO `siteData` (`flr`, `fd` ,`dir`,`inS`,`devId`, `ts`) VALUES ('$_POST[FLR]','$_POST[FD]' ,  '$_POST[DIR]' , '$_POST[InS]' ,'$_POST[id]', '$_POST[date("Y-m-d H:i:s",ts)]'  )";
1 Like

Tried it as you coded it and I got an error so I broke it out line by line. Seems the date() function isn’t converting correctly.

echo $_POST[ts];
$mysqltime = date ("Y-m-d H:i:s", $_POST[ts]);
echo $mysqltime;
2018-10-11T12:50:34.877Z1970-01-01 00:33:38

Here is the fix:

echo $_POST[ts];
$mysqltime = date ("Y-m-d H:i:s", strtotime($_POST[ts]) );
echo $mysqltime;

The strtotime() was missing.

Thanks alot for your help. Saved me a ton of time.

1 Like