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?
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')
We have been trying to get this to work but we are running into 2 errors. We are getting back a null field value when we test it in our dashboard. Either I don't understand how to return things, or I don't know where to put in the field I want to change which is (bcs_Response_Date) . Also, I don't have the function to Change Time Zone.