May 29, 2019 04:49 AM
Hi,
I am working on a check in/ check out system for a local studio. I’ve hit a problem that I can’t find a solution for elsewhere.
The image below shows the problem. The second row should show a “ :white_check_mark: ” as the Last Check In is 1 minute after the Last Check Out, however the difference between the two times (using DATETIME_DIFF) is showing as 58 minutes.
I think the problem is caused by DATETIME_PARSE on another table not correctly setting the timezone (see below). If I have “GMT for all collaborators toggled on” it shows the correct time (as above) but is still being treated as the correct time + 1 hour
Any ideas what is going on here? Or how to get around the parsing issue? Any help would be greatly appreciated!
Ross
May 29, 2019 11:43 AM
I have yet to understand fully Airtable’s datetime manipulations — that is, I still can’t predict when calculations need to be forced to GMT. (I assume you’re in an area an hour off GMT.) Usually it’s hit-and-miss: If I can’t seem to find a combination of ‘Use GMT’ toggles that returns the right value, I assume derived values are in GMT and shift entered values to match.¹
There’s a simple DATETIME_PARSE()
trick that allows you to standardized datetime stamps for calculations without having to hardcode time differentials — and, thus, risk corrupted data twice annually when DST goes into effect. (Assuming it does where you live.) See the second item in this ‘tips and trick’ reply from a while back.
May 31, 2019 01:08 AM
Thanks for the reply. Will have a look through everything! Hopefully this is something Airtable will improve at some point :slightly_smiling_face: