Recording a created_at date for each point

1

Hi. Thank you all for the help so far. My goal is to record some data with timestamps, having the device sleep, do it again, and then periodically send it off to Adafruit. I think I have most elements down. But, I’m trying to get the data timestamped at the time it was taken (not the transmitted time). I can turn on the GPS and that looks great. But, if I want to add a created time?

long UNIXtime = 0;
rsp = notecard.requestAndResponse(notecard.newRequest(“card.time”));
if (rsp != NULL) {
UNIXtime = JGetNumber(rsp, “value”);
notecard.deleteResponse(rsp);
}

Not surprised that it doesn’t work, but it was my best guess. I’m also concerned that I get the current time, not the time of the last contact with the GPS.

Thanks,
Brecky

2

Hi @brecky,

If you look in the JSON of a given event in Notehub, you’ll see three timestamps automatically added:

"received": 1646767303.647652,
"routed": 1646767303,
"when": 1646767301,

when is the timestamp you want - this is when the Note was created on the Notecard.

3

Hi,
I’m looking to do the same thing with ThingSpeak but I don’t know how to construct the JSONata expression to include the “when” timestamp. I’ve tried this but it doesn’t work.
{
“created_at”: when,
“api_key”: body.key,
“field1”: body.weight,
“field2”: body.temp,
“field3”: body.bat,
“field4”: body.soc,
“field5”: body.netrawwt
}
Without the [“created_at”: when] line, the route works fine but the timestamp becomes the default “routed” time. Can someone please set me straight?

4

hey @rberkelm that should work, but a couple of questions:

  1. What is the response message your get from ThingSpeak when “created_at” is included?
  2. Is your route set-up to only send events from the notefile you are using with that body above?
5

In my setup there are 6 notefiles that get sync’d to Notehub once per hour. Out of those 6 notefiles, only one gets routed successfully to Thingspeak. The rest don’t make it. The route response for those is: {“status”:“200”,“text”:“0”}. The time stamp on the successfully routed note is always the “routed” timestamp, not the “when” timestamp. Yes, to your second question.
There may be two issues at play here. One is the timestamp that remains the “routed” timestamp. The other is the frequency of routes to Thingspeak which has a limit of 1 per 20s. Batch uploads are allowed but the format needs to be like a csv table from what I can tell.

6

Ok, just an update on the “created_at” timestamp issue. It turns our that ThinkSpeak ignores the “when” timestamp because it is in epoch format, not the ISO8601 format (e.g. 2014-12-31 23:59:59) that it wants. It then creates its own timestamp which is close to the “routed” timestamp. So, unfortunately we can’t use the automatically generated timestamp. Instead we have to create our own.
In my case:


#include <TimeLib.h>
char timeString[32];

void setup() {


   //time from Notecard
      {
    JTIME ncTime = 0;
    J *rsp = notecard.requestAndResponse(notecard.newRequest("card.time"));
    if (rsp != NULL) {
       ncTime = JGetNumber(rsp, "time");
       notecard.deleteResponse(rsp);
    } 
    //print long time
    sprintf(timeString, "%02d-%02d-%02d %02d:%02d:%02d", year(ncTime), month(ncTime), day(ncTime), hour(ncTime), minute(ncTime), second(ncTime));
    serialDebugOut.println(timeString); 
      }

    // add note
    J *req = notecard.newRequest("note.add");
    if (req != NULL) {
        JAddBoolToObject(req, "sync", true);
        J *body = JCreateObject();
        if (body != NULL) {
            JAddItemToObject(req, "body", body);
            JAddNumberToObject(body, "temp", air_temp);  
            JAddNumberToObject(body, "bat", voltage);
            JAddNumberToObject(body, "soc", soc);
            JAddNumberToObject(body, "weight", Weight);
            JAddNumberToObject(body, "netrawwt", NetRawWt);
            JAddStringToObject(body, "key",api_key);
            JAddStringToObject(body, "createdTime",timeString);
        }
      notecard.sendRequest(req); // send and sync
    }

}

void loop() {
}

And changed my JSONata to:
{
“created_at”: body.createdTime,
“api_key”: body.key,
“field1”: body.weight,
“field2”: body.temp,
“field3”: body.bat,
“field4”: body.soc,
“field5”: body.netrawwt
}

Hope this helps anyone else stuck on this. :slight_smile:

1 Like
7

Hey @rberkelm, it’s actually possible to do a conversion of the when field in JSONata to get an ISO date, like so:

{
  “created_at”: $fromMillis(when*1000),
  “api_key”: body.key,
  “field1”: body.weight,
  “field2”: body.temp,
  “field3”: body.bat,
  “field4”: body.soc,
  “field5”: body.netrawwt
}

Hope that helps!

Brandon

3 Likes
8

That is excellent! Much more elegant - thank you!

9

A bit long in the tooth . . . but to cycle back to the original question - is there a key/field in a JSONata modified payload that an adafruit.io MQTT feed would use instead of the received date/time ? Thanks. --adam

10

Hi @abkhi and welcome to the Blues community!

Earlier in the thread I mentioned three different timestamps that are available by default:

Is that what you are looking for?

Thanks,
Rob

11

That was the answer that grabbed my attention. It’s going further that i’m looking for - does Adafruit have the ability to use the “when” timestamp when it plots a time series (as opposed to it using the time it received the data). I realize i may need to get that info from Adafruit formum - but i’m hoping somebody here knows.

And in a related way: does anybody know of an inexpensive (doesn’t need to be free) cloud based alternative to adafruit.io that would have the capability to use a timestamp from the data payload as the x-axis value.

Thanks. --adam

12

Hi @abkhi,

I would check on the Adafruit forums re: the ability to use a different date in the payload vs their default “received” date. I’d be surprised if it weren’t possible (or maybe you can use a JSONata expression in your Notehub route to massage the data into the format Adafruit needs?).

In terms of other IoT/cloud platforms, I’ve had great experiences with Datacake, Ubidots, and Losant in the past (Datacake and Ubidots especially being very fair in terms of pricing). They can all handle processing arbitrary dates that you send (either using JSONata or in the case of Datacake using their payload decoder). Quick example with Ubidots and using JSONata to transform data on the Notehub route:

{
    "temperature": {"value": body.temperature, "timestamp": when * 1000},
    "voltage": {"value": body.voltage, "timestamp": when * 1000}
}

Hope this helps,
Rob

13

Hi Adam,

I think this is the bit that you want:

“created_at”: $fromMillis(when * 1000)

, but I’ll put my entire route here:

{“feeds”:[
{“key”: “temperature”, “value”: body.temp},
{“key”: “pressure”, “value”: body.pressure},
{“key”: “voltage”, “value”: body.voltage},
{“key”: “card”, “value”: body.card},
{“key”: “humidity”, “value”: body.humidity},
{“key”: “temperaturers485”, “value”: body.temprs485}
],
“location”: {“lat”: where_lat, “lon”: where_lon, “ele”: 0},
“created_at”: $fromMillis(when * 1000)}

Brecky

14

@RobLauer and @brecky : Thanks much for the input. I’ll give those routes a try, and see what i can do on the Adafruit.io side. I’ll also check out the Ubidots and Datacake offerings.
–a