Date/time field shows wrong time in Automation

[Gio_Coaching]

Recently, we found that Date field has "Time Zone" option. Therefore, we set the time zone as "Asia/Seoul" of the date field and we got rid of another converted time field since it is not necessary any more. 

However, the problem occurs when we send an automated email using this date field. 

• In the Airtable field, we put this value: 2023-01-10 10:00 KST
• When we send an automation, the mail shows this: 2023-01-10 01:00 UTC

Do we need to make another field which has the converted time to show correct time in Automation, or is there a way to show a correct time in Automation with the specified time zone in the field?

[Karlstens]

What Automation trigger are you using that triggers the event of sending an email, and how is it configured?

[Gio_Coaching]

Thanks. Here are the brief of my Automation (Review of today's training events):

1. Automation trigger: At a scheduled time (every week) 

2. Action 1: Find records 
where start_date is on or before today and end_date is on or after today

3. Action 2: Send an email

I attached screenshots of the field setting, and automation settings. Please help.

[Richard_Willia1]

did you ever find a solution to this?

[Gio_Coaching]

Not yet. The time in the automation does not reflect the time in the date field with a proper time zone. 

[Richard_Willia1]

Thanks - it's just madness how airtable don't seem to get the importance of being able to use dates/times reliably. 

I have worked out a dirty solution that works - I take the HTML generated by the grid view and edit it with a script action to update the times. It doesn't feel like a long term solution though - my code below..

let config = input.config();

var shifts = config["shifts"]; //this should be a "grid view" passed in from a previous automation step

//console.log(shifts);
//find >xxxUTC<
while (1) {
  var endIndex = shifts.indexOf(" UTC<");
  if (endIndex == -1)
    break;
  var startIndex = shifts.indexOf(">", endIndex - 20);

  if (startIndex == -1)
    break;

  let dateTimeStr = shifts.substring(startIndex+1, endIndex+4);

  var options = {};
  options.timeZone = "Europe/London";
  let dateConv = new Date(dateTimeStr).toLocaleDateString("EN-GB", options);
  let timeConv = new Date(dateTimeStr).toLocaleTimeString("EN-GB", options);
  console.log(dateConv+" "+timeConv);

  let start = shifts.substring(0, startIndex+1);
  let end = shifts.substring(endIndex+4);
  shifts = start+dateConv+" "+timeConv+end;
} 

output.set('table', shifts);

[halffrozen]

Having the exact same issues. My data/table is showing the correct time from my Interface form entries, however when the automation sends the email, it changes my time zone from MST, to UTC, thus showing the incorrect time in the email. Does Airtable really not have this basic ability to take user defined data, and just... send it correctly? lol

[Richard_Willia1]

It is crazy how airtable does some basic things so terribly. I think there is a way to solve this with a formula field which looks up the data - or with the script I noted above. But it’s pretty terrible