Parse a date while keeping the European date format


#1

I’m struggling with dates, and suspect it might have to do with the different conventions for describing dates. Here in NZ (and in most of Europe) we use DD-MM-YYYY whereas in the US you use MM-DD-YYYY.

Rather than describe in words, it will be easier to show you these screenshots that tell the story.

I’m trying to present a date (that is recognised as a date), that is stripped of the time details in the DateTimeStart field.


#2

It looks to me like it’s an issue with time zones - for me in the UK the last DATETIME_FORMAT works correctly for all times of the day - but then I am in the UK on the date line!

There is a support article about this though:

I hope this helps - it looks as though they have thought about it!


#3

Thanks Julian, yes it seems the problem lies in Airtable assuming I’m in London :stuck_out_tongue:
I wonder how I can tell them where I am so when I enter a date and time it is saved correctly!


#4

Hi James

I’ve had a bit of a play around with this and the Set_Timezone function doesn’t seem to work as expected - I think this is one for Airtable to answer!


#5

Cheers Julian - nice getting to know you - I hope I can return the favour one day :slight_smile:


#6

I’m sure you will get the chance!

You may be interested - I have literally just now created a new Linked in Group called Mobile and Web Databases which is intended to be a forum for people using services like Airtable (and others) as well as Zapier etc. I’m hoping that it eventually becomes a great place to share experience across different platforms, find reviews etc.

Your welcome to join (as is anyone else reading this):

https://www.linkedin.com/groups/8572976


#7

Still working this problem through with Victoria at Airtable - when we get a resolution I’ll let you know.


#8

Hey guys! Is this an official bug? It looks like SET_TIMEZONE doesn’t do anything…


#9

Hi Tuur,

Try DATETIME_FORMAT(SET_TIMEZONE({Some Date Field}, ‘YourTimezone’), ‘HH:mm’))

Alex


#10

It’s a bit strange. When I use something like:

DATETIME_DIFF(DATETIME_FORMAT(SET_TIMEZONE({Value}, ‘Europe/Amsterdam’), ‘YYYY-MM-DD HH:mm’), NOW(), ‘minutes’)

I get a 240 minute difference when my own value is now…

DATETIME_FORMAT(SET_TIMEZONE(Value, ‘Europe/Amsterdam’), ‘YYYY-MM-DD HH:mm’)

Gives me a timestamp that’s 2 hours later than my original value.

Airtable takes GMT by default or something?


#11

Yup.

DATETIME_FORMAT(SET_TIMEZONE(NOW(), ‘Europe/Amsterdam’), ‘YYYY-MM-DD HH:mm’)

Works.

Cheers @Alexander_Sorokin


Force a timezone with DATETIME_PARSE? How about NOW()?