What is the correct way to use the API to send timestamps?

Dev_Local · ‎Mar 05, 2020

Using the Airtable API, I am sending a timestamp exactly like this (on an insertion of a record/row):

{“fields”:
{“Some Timestamp”: “2020-03-05 04:00”}
}

How is this interpreted by Airtable? I want it to be interpreted as: “2020-03-05 04:00 PM UTC”

My time zone is MT and so locally my time is the same day but 7hrs earlier than UTC: 2020-03-05 09:00 AM)

When sending the above to Airtable it is displaying the day before (3/4/2020):
Displays: “2020-03-04 09:00 PM”

This is a time of 12 hrs BEFORE my local MT time zone.

What is happening here?

How does it go from: “2020-03-05 04:00” to 19 hours previous as “2020-03-04 09:00 PM”
(which is 19 hrs earlier than the time I am sending it, if 04:00 is interpreted as PM or 7 hours earlier if it thinks it is AM)

Should I be sending anything additional with the time stamp, other than: “2020-03-05 04:00”

The field is defined as:

Date
Date Format: Displays (today): Local (3/5/2020)
Time Format: 12 hr
off: Use the same time zone (GMT) for all collaborators.

Bill_French · ‎Mar 05, 2020

Actually, I don’t think it is. Airtable has taken your 9:02p time and posted it into the next day (the 5th) at 4:02a. This is exactly +7 hours and correct.

Dev_Local · ‎Mar 05, 2020

It’s showing the previous day, not the next day.

The time my process ran was: 3/5/2020 9:00 AM MT

The time put into the database (programatically - UTC) was +7 seven hours later: 3/5/2020 :400 PM UTC. that is fine, that is what I would expect.

Now I take this UTC time: 3/5/2020 4:00 PM UTC and send it to Airtable

Airtable is showing the day BEFORE (incorrectly), it is showing 3/4/2020 : 9:00 PM

I am tracing this all the way through my code to from what is stored in the DB, what is being pulled out and put into the JSON that is being sent to Airtable.

Does Airtable change times we send it? and how and why? Is it detecting some time zone in the browser?

Bill_French · ‎Mar 05, 2020

Difficult to say what the issue is without seeing code.

Whatever you send it, it will store the date in UTC. This is a fairly entrenched standard for many reasons.

kuovonne · ‎Mar 05, 2020

The time stamp you are sending does not include the time zone, so you are at the mercy of whichever computer interprets it, which depends on how your code is written.

Format your date/time string according to the ISO 8601 standard, which includes a time zone.

Dev_Local · ‎Mar 05, 2020

Thank you,

I tried once before sending the raw data from the DB which is stored as ISO 8601, and this failed. I will give this another try.

I am using Postgres, the table definition is : not null default now which stores as UTC ISO 8601, selecting that out unmodified and sending to Airtable has failed in the past.

Bill_French · ‎Mar 06, 2020

Okay, well - this is why these additional details are important. Difficult to help you efficiently when we lack the true scope of the challenge. Postgres has some unique date formatting requirements that don’t blend well with Airtable.