Dates and Time with the API

Ask the Community
#1

Hello all,

Thank you for providing such a rich resource.
I am having some great success with my initial use of the API, but I am really struggling with the Date format.
If I write to a column just the date via the API and then use a second column with the ToDate() gormula it works, but if I add time to the data I have no success.
I also cannot write directly to a date field and have the data recognised as a date.

My questions are:

  1. Is it possible to write directly to a date field via the API? If so, what is the format expected?
  2. If the above is possible, how should a date and time format be formatted so it is recognised? Also, does the ToDate() formula recognise Time and Date?
#2

hi @Chris_H

I do not have specific info about the API. My contribution is however related to a comparable issue. Once I use AddRow and I create a new Row in an other table that contains the date value I am used to (EU format, thus 11/02/2021 (Feb 11, 2021), it looks okay. But when I try to work with it in the new table, it is not okay, even not when I set the column as date.

To make this date a value I can work with, I apply the following, I thus reorder the values to bring them from my EU format back to the USA format. Once this is done in the second Column, it works.

Concatenate(thisRow.[injected dates].Split("/").Nth(1).Trim(),"/",thisRow.[injected dates].Split("/").Nth(2).trim(),"/",thisRow.[injected dates].Split("/").nth(3).Trim()).ToDate()

If you write to a date field, I guess you have to use the USA format, the one Coda uses everywhere
If you want to add the time, you have to follow also the USA standards and it might work fine.

I hope it helps a bit

The doc in which I apply this date logic, you find overhere: HolidayPlanning

best, Christiaan

1 Like
#3

You should be able to use the format yyyy-mm-dd, e.g. 2021-02-25. For date and time it’s yyyy-mm-ddThh:mm:ss, e.g. 2021-02-25T18:30:45. This is the standard ISO-8601 format.

At least this works if

  • I copy a row from a table with date/date and time columns which use the EU-format or any other.
  • Paste it in a text editor and change the date/date and times to the above mentioned format
  • …copy from the text editor and paste it as a new row into the table.
1 Like
#4

Thank you both for your input, as is often the case with these things I eventually stumbled across the right answer, which I post for anyone with the same problem.
I found the easiest way was to insert via the API an Epoch number for the date and time.
The Coda format for Epoch calls for 10 digits, where most languages produce a 13 digit Epoch value.
This is what worked for me:

EpochToDate(left(thisRow.[Date Input],10))

I managed to utilise a Node-Red setup that scrapes data from a website, format it and then post it into Coda via the API, which works brilliantly.

1 Like
#5

thanks @Chris_H , I had to look it up and found it overhere:

The number of seconds since Jan 1st, 1970 and not earlier! :wink: