I have a Date column in my table. When I insert a new row with the date in it, Seatable is not retaining the AM & PM. I am inserting my date as “12/13/2024 10:12:54 AM” for example, and Seatable just shows it as “12/13/2024 10:12”. It discards the seconds & the AM/PM, so I have no idea of the exact time the row was added.
Can anyone help me with this?
Hi @nbrege,
Here is a little workaround, even if I’m not sure it will fit your actual needs:
- Enter your date in a text-type column, so you’ll be able to keep your input format (“12/13/2024 10:12:54 AM”). For the next step, I will assume that this column is named
date input. If it’s not, please replacedate inputeach time it appears in the following formula by the actual name of your date input column - Then, create a new formula column with the following formula:
hour({date input})*3600+minute({date input})*60+second({date input}) - If it doesn’t automatically, Edit the format settings of this column, re-detect should return number so you’ll be able to choose Duration for Format and h:mm:ss for Duration format
It will return the full time in a 24h mode (no more AM or PM, but still a difference between 10 and 22). The date however is not included. You can of course create another formula column to get the actual date from your date input text column, but date and time will still be two separated columns, I don’t know if this is acceptable for you… We can also imagine a solution with a script, allowing to use the python or JS datetime functions/objects, but it sounds a bit too much 
Bests,
Benjamin
I don’t understand the issue.
SeaTable’s date column type does not support the AM/PM notation.
Additionally, why don’t you simply use the “created time” column type?
This ccolumn captures the exact time a row was created.
If there is no AM/PM indicator then how know when a time is?
So if the time is just stored as 10:00, how do you know if it’s 10:00 in the morning or 10:00 in the evening? AM/PM is a standard in MS SQL Server. Totally baffled why Seatable doesn’t have.
Also, a “created time” column might work for some situations, but what if I wanted to store a date/time that was different from the created time?
Here is a screenshot of how Seatable is storing the date & time. The column is a Date column set to US format. I have no idea if these are AM or PM.
As far as I know, time is always 24h based in SeaTavle, so 10 is necessarily AM (10 PM being 22). I think the “US” format only applies for date (MM/DD/YYYY), not for the time…
Bests,
Benjamin
1 Like
The US format DOES store time, as you can see in my screenshot above, just not properly. You would think that if they offered the option of US format they would have implemented it properly. It’s useless as it is currently. If I query my data & order by Date I wont get the rows back in the proper chronological order. This is a major flaw.
Hi again @nbrege,
You misunderstood me I’m affraid: I didn’t say that the US format doesn’t store time, but that only the date is formatted as expected for the US format, not the time, being 24h-based instead of the usual 12h-based AM/PM formatting for US.
Anyway, I probably don’t have a whole picture of your use case, but as far as I tested, trying to order data from query based on a simple text column seems to work with your format (“12/13/2024 10:12:54 AM”), isn’t it ?
Bests,
Benjamin
1 Like
The time does not appear to be stored in 24 hour format. There are no times greater than 12:59 being saved. If it was 24hr based then the next time should be 13:00, but it goes back to 1:00 instead. It appears to be 12hr based, but just without the necessary AM/PM.
rdb … what time zone does the Created Time column use?
Of course, it is.
If you don’t believe my screenshot, I encourage you to invest 10 seconds and try yourself.
In the DATE column’s settings, you can select the DATE format. You cannot set the time format. The time format is always 24h notation.
Not in my table it doesn’t. There is no saved time greater than 12:59. If it was in 24hr format it would go to 13:00, but instead it goes to 01:00. See screenshot below…
If it was in 24hr format the 01:02 should be 13:02. If the US format option was implemented properly then it should say 01:02 PM, not just 01:02.
Hi again @nbrege, I’m affraid we’re all misunderstanding each other…
Regarding your last post, I understand that you never see any time greater than 12:59 appearing in your table. But reading back your first post
I understand that this happens because you insert the time using the AM/PM formatting you mentioned in the first post, am I correct ? To ask it more clearly : what was the input of the second row of your last post’s screenshot ? 12/26/2024 01:02 PM or 12:26:2024 13:02 ?
Because I think (but once again maybe I’m wrong) that @rdb and I both are saying that the time in SeaTable date column is 24h formatted because it can actually display times greater than 12:59 (see for example @rdb’s last screenshot), but you say that it’s not 24h formatted as you expect a 24h formatted column to automatically convert an AM/PM formatted input to a 24h formatted output, isn’t it ?
If it’s not, sorry but I still don’t understand how you managed to get a 12h formatted date/time column, and if it’s actually the case, let me explain the date/time format like this (I hope it will be clear enough):
The Date column, once “accurate to minute” is checked, is expecting the following input formatif you selected US format MM/DD/YYYY HH:mm
- MM : month (single digit for month before October)
- DD : day (single digit for day before the tenth)
- YYYY : full year (4 digits)
- HH : hour (two digits), 24h formatted
- mm : minutes (two digits)
Given this, everything you put after the minutes digits, being AM/PM or anything else, won’t be considered
Once again, even if I understand that this column not behaving as you expected could be really annoying for you, I really think a pretty simple workaround could be found (already few of them have been discussed) to match your needs.
Bests,
Benjamin
1 Like
I assume your issue has been dealt with in sufficient detail. We’ll close the topic within the next days.
Yes, I believe so …
Also, the 20 character minimum is very inconvenient. It wouldn’t let me answer with a simple “Yes”.
Thanks for the feedback. Minimum post length is now 3.
This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.
