Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Does 'Date' field not record seconds?

Topic Labels: Dates & Timezones
Solved
Jump to Solution
4636 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Blark
5 - Automation Enthusiast
5 - Automation Enthusiast

It would appear that the ‘Date’ field does not record seconds.

Here’s why I think this:

Screen Shot 2020-05-25 at 3.05.50 PM

Here are the formulas I’m using to expose this:
DATETIME_FORMAT({Date (no secs?)}, ‘DD-MM-YYYY h:mm:ss’)
DATETIME_FORMAT({Created Time}, ‘DD-MM-YYYY h:mm:ss’)

I hope I’m wrong about this. I need to be able to grab seconds from a ‘Date’ record.

1 Solution

Accepted Solutions

You are correct, that clicking “today” in the date/time picker only enters year, month, day, hour, and minutes. Seconds are set to zero.

I used that exact same method when entering the date/times in my screen capture. Then I used a formula field to show how Airtable stores the date/time value internally in ISO 8601 format. You can see the trailing zeros for zero seconds in the screen capture.

I’m afraid that clicking “today” will not work for your use case.

See Solution in Thread

7 Replies 7

Wow, that’s very interesting.

Looks like “seconds” are supported in the “Created Time” field and “Last Modified Time” field, but not in a normal “Date” field.

Looks like you’ll need to use the “Duration” field, which supports hours, minutes, and seconds.

It also seems like it would make sense for Airtable to open up the normal “Date” field for seconds as well. You may want to add this as a feature request in the Product Suggestions topic.

p.s. If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Date/time field do store seconds, and even fractions of seconds. However, there is no way to enter seconds in a date/time field through the user interface. Thus, whatever date/time you enter, there are no seconds, yet the "created time"field does have seconds.

This shows how date/times store seconds to three decimal places.
image

You can get the seconds from a date/time field using the SECOND() function in a formula field. It will be an integer from 0-59.

However, it seems the only way to enter a date/time with seconds is through one of the APIs, or to grab it from a {created time} or {modified time} field/function.


If this answers your problem, please mark this post as the solution. Otherwise, could you please give a bit more details on where your date/time comes from?

Blark
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you. My date/time comes from clicking into my first column (Date), and then clicking “Today” from the calendar.

It sounds like doing that only enters year, month, day, hour, and minutes?

How did you get your seconds to show? I’m guessing from what you said your ‘date/time’ column comes from an API?

Here’s what I’m trying to accomplish ultimately: Elapsed time / stop watch / timer

Blark
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks @ScottWorld: I added this to Product Suggestions, per your suggestion: Support seconds in Date field

Hi @Blark

Did you see the reply to your post?

Thank you.

You are correct, that clicking “today” in the date/time picker only enters year, month, day, hour, and minutes. Seconds are set to zero.

I used that exact same method when entering the date/times in my screen capture. Then I used a formula field to show how Airtable stores the date/time value internally in ISO 8601 format. You can see the trailing zeros for zero seconds in the screen capture.

I’m afraid that clicking “today” will not work for your use case.

Right, what @kuovonne was saying is that you can use the SECOND function to pull the seconds out of a “creation date” field or a “last modified date” field (and you can do this, but you can’t pull any seconds out of a normal “date” field because Airtable sets it to zero.

However — it sounds like if you set the date field through the API, then you CAN set the seconds to the “date” field via the API. That’s a cool trick to know about — I’ve never tried that before!