How to convert time from metada

Gateways
1

Hello,

I actuelly getting data from my getway to my remote server.As intergration, I use HTML.

I got a json filr format and I useing the time in metedata.
2018-05-22T16:36:31.959545235Z

I save all data into my gateway and I have a table names ‘collection’. A collection is a collection of measures sent at the same time through the gateway.

In order to make sure, I not have double same entry, I make a check comparing the data/time

In my table, the following
2018-05-22T16:36:31.959545235Z
is saved as
2018-05-22 16:36:31

the number after the dot is removed (is normal)

In any case, when I use a MySQL request in php, to see if the entry exist, it never work
$sql_selectc = ‘SELECT c.collection_date,c.ttn_m_time FROM collections AS c
WHERE c.ttn_m_time = "’.$m_time.‘“”
ORDER BY c.collection_created ASC’;

Then, I wunder, if there is way to remove

2018-05-22T16:36:31.959545235Z

to

2018-05-22 16:36:31

or

2018-05-22T16:36+0200

With PHP/MySQL

Or if there is another way to compare a entry with the date/time and the metada.time of the json file.
Or how would you do it?

Cheers
.

2

Where are you experiencing double entries? The HTTP Integration sends one ping to your WebHook; so only your script can create double entries (or something inbetween does a terrible job)

IF you do get double calls from the HTTP integration it is better to check the counter metadata, that increments on every ping, a simple Integer comparison and not a costly Date comparison.
LoraWan Frame counter: https://www.thethingsnetwork.org/docs/applications/http/

So you are using an older MySQL version?
I know MySQL 5.6 would not store fractional seconds

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

But (depending on when you get double entries)

$sql_selectc = ‘SELECT c.collection_date,c.ttn_m_time FROM collections AS c
ORDER BY c.collection_created ASC’ LIMIT 1;

Should get the previous entry