Returning Time Zone relevent timestamps, when server manages multiple time zones.

Question

We utilize a single QVERA server that manages interfaces for sites across multiple time zones.

the CPS sql servers are unique per time zone so the data all reflects that databases 'current' time zone.

However we have a vendor that is asking us to return specific values compared to GMT.

I do see the tags that will handle this  (ie: yyyy-MM-dd'T'HH:mm:ss.SSSZ)  however that would be based off of the time zone on the QVERA server, not the specific database.

Is there a way in the zone, channel, or even mapping that we can define the local time zone to allow the tags to properly relfect the correct timezone?

Answer 1

When querying from the specific database, you will need to know the timezone for that database.  With that information, you will need to add the timezone to the end of the string that is returned from the query.

For example.  If I have a SQL server returning dates in EST timezone, I would query the database:

SELECT time FROM some_table;

The resulting time might be '2017-06-05 13:15:45'

I would append the EST so that it would be '2017-06-05 13:15:45 EST'.  Now I can deduce the date into the current QIE timezone.

Once I have the date in the current timezone, I can convert it to 'UTC' by following the examples in this KB article:  https://www.qvera.com/kb//index.php/127/is-there-a-way-to-change-the-time-zone-of-a-date-time-value

Here is a code example:

// get the date from the query
var sqlDate = qie.doConditionQuery('myDatabase', 'SELECT timestamp FROM some_table');

// append the timezone from the SQL server.  In this case the server 'myDatabase' with the timezone of 'EST'
var sqlDate = sqlDate + ' EST';

// now conver thte date to the local QIE date
var qieDate = qie.deduceDate(sqlDate);

// now we need to output the date as a formatted UTC date
var utcTimeFormat = new java.text.SimpleDateFormat('yyyy-MM-dd HH:mm:ss.SSSZ');
utcTimeFormat.setTimeZone(java.util.TimeZone.getTimeZone("UTC"));

var utcDate = utcTimeFormat.format(qieDate);

// utcDate can be placed in the message and the receiving system can process it as UTC