Using Arcade to change string field to a date field in Dashboard

ThomasRickett

We have 2 webservices pulling data into GeoEvent from our SQL databases. The issue is that the format of the Date field coming from SQL isn't in a time format that is recognized by GeoEvent.  The field needs to be parsed from a string into a date field in order to then use Date functions. 

The format is (Day of Week in 3 letters, DD-MM-YYYY hh:mm:ss TZD)

How do I do that? So I can then use date functions to go from GMT to CST?

jcarlson

With a consistent format, you can do this with Split. That will get your string into is component parts, which you can reference by index.

var date_parts = Split('Wed, 16 Nov 2022 21:14:10 GMT', ' ')

var the_day = date_parts[1] // '16'
var the_month = date_parts[2] // 'Nov'
var the_year = date_parts[3]  // '2022'
var the_time = date_parts[4] // '21:14:10'
var the_tz = date_parts[5] // 'GMT'

 

Building a date from that is easy enough, though the month and time need more work.

var time_parts = split(the_time, ':')

var the_hour = time_parts[0] // '21'
var the_minute = time_parts[1] // '14'
var the_second = time_parts[2] // '10'

// Convert month string to numeric equivalent; months are 0-indexed
var month_number = Decode(
  the_month,
  'Jan', 0,
  'Feb', 1,
  'Mar', 2,
  'Apr', 3,
  'May', 4,
  'Jun', 5,
  'Jul', 6,
  'Aug', 7,
  'Sep', 8,
  'Oct', 9,
  'Nov', 10,
  'Dec', 11,
  0
)

// create a date; convert each to number
var gmt_date = Date(
  Number(the_year),
  month_number,
  Number(the_day),
  Number(the_hour),
  Number(the_minute),
  Number(the_second),
  'Etc/GMT'
)

 

If you're sure that everything's coming in from GMT and going out as CST, you can use ChangeTimeZone.

var cst_date = ChangeTimeZone(gmt_date, 'US/Central')