Re: Date fields and REST API

Dev_Local · ‎Dec 23, 2019

If I have a field customized to this type:

1. Date
2. Time component
3. 12 hr

and I use the key/value pair in my JSON/POST, what do I send there to get this to work?

I have this working if I send a string in the correct format, but as soon as I include "typecast": true" it fails.

I am including "typecast":true to get fkey lookups to work.

I have a field in a Postgres database defined as:

inserted_date TIMESTAMP NOT NULL default now()

and then I am pulling it out like this (string), to send to Airtable using the REST API (POST):

(select to_char(table_name.inserted_date, 'MM/DD/YYYY HH12:MI am'))    "Inserted Date",

and it works, but as soon as I include: "typecast":true, it fails.

If I pull out not using select to_char(... , and just use: <table_name>.inserted_date it fails also.

It appears I need to send a native date format, time component, but what would that be?

Having a field defined as Date, 12 hr time component, and also when using typecast, these Strings (in the JSON) will not work, I get a failed API call - (but is valid JSON):

1. "Inserted Date": "12/20/2019 04:10"
2. "Inserted Date": "12/20/2019 04:10 pm"
3. "Inserted Date": "Fri Dec 20 2019 16:10:09 GMT-0700 (Mountain Standard Time)",

Not using a String, but the Node.js Date type that comes back from Postgres is invalid JSON, and also can not be used.

"Inserted Date": Fri Dec 20 2019 16:10:09 GMT-0700 (Mountain Standard Time),

having the field defined as Date, 12 hr component, not using typecast, this String works:
"Inserted Date": "12/20/2019 04:10"

When typecast is specified, what is happening when it encounters a Date/12hr field?

JonathanBowen · ‎Dec 28, 2019

Hi @Dev_Local - looking at the API documentation for a date field you need to send it in this format:

  "fields": {
    "Date": "2019-12-30T21:42:00.000Z"
  }

The 12 hour time setting appears to be a just a UI setting (along with the date format) - the date is stored in Airtable in the above ISO format. From a Postgres database this would be something like:

YYYY-MM-DD HH24:MI:00:000Z

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:00:000Z');

(seconds and milliseconds set to zero as AT is not storing these).

JB

JonathanBowen · ‎Dec 28, 2019

In my tests, the "typecast": true setting made no difference, i.e. it worked with and without.