Skip to main content

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 “ ✅ ” 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

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.




  1. Note that turning on and off the GMT toggle isn’t the same as changing how the field is formatted: It actually changes how entered data is stored. So flipping that toggle, entering a new datetime, and flipping it off will have interesting effects on the relationship between existing and newly entered data. If you’re modifying a working base, it’s best to make your changes on a copy of the base and then transfer the working code back to the original — which is probably not a bad practice to get into even when not working with datetime fields.

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.




  1. Note that turning on and off the GMT toggle isn’t the same as changing how the field is formatted: It actually changes how entered data is stored. So flipping that toggle, entering a new datetime, and flipping it off will have interesting effects on the relationship between existing and newly entered data. If you’re modifying a working base, it’s best to make your changes on a copy of the base and then transfer the working code back to the original — which is probably not a bad practice to get into even when not working with datetime fields.


Thanks for the reply. Will have a look through everything! Hopefully this is something Airtable will improve at some point 🙂

Reply


Terms & Conditions