I am having an issue when importing data from a SQL table using the Query Databa

f you have a database that stores datetimes and does not convey information about what time zone those datetimes are in, then it's up to the application to make some assumptions about what time zone the datetime is in.

It's no different for Appian when our app is the one interfacing with the RDBMS. Appian takes care of the shifting to the context time zone when using datatimes stored in PVs, so the only touchpoints you need to worry about are those with the RDBMS: when storing datetimes to the RDBMS and retrieving datetimes from the RDBMS.


1. To feed datetimes to the database, take the datetime stored in GMT (e.g. the pv, the result of a function) and shift it to the time zone that the database expects. Going from GMT to a local time zone requires adding the offset to the GMT representation of the datetime. You can do this with the local() function:

local(pv!DATETIME_TO_INSERT_GOES_HERE,"Australia/Sydney")

2. When you retrieve a datetime from the RDBMS, you need to find the GMT equivalent of the datetime you get from it and store that in the PV for display purposes, otherwise Appian will assume the database is sending the data in GTM and will do an adjustment that will give you the wrong timestamp.

When you have a local datetime and you want to know the GMT equivalent of it, you subtract the time zone offset from that local datetime. The way you achieve this shifting (from a local time [RDBMS time zone] to the gmt equivalent) is using the gmt() function and passing the time zone representing the offset from GMT as the second parameter. So


gmt(pv!DATE_RETRIEVED_GOES_HERE,"Australia/Sydney")

Here is a screenshot explaining the insert: forum.appian.com/.../79244
Here is a screenshot explaining the select: forum.appian.com/.../79246

f you have a database that stores datetimes and does not convey information about what time zone those datetimes are in, then it's up to the application to make some assumptions about what time zone the datetime is in.

It's no different for Appian when our app is the one interfacing with the RDBMS. Appian takes care of the shifting to the context time zone when using datatimes stored in PVs, so the only touchpoints you need to worry about are those with the RDBMS: when storing datetimes to the RDBMS and retrieving datetimes from the RDBMS.


1. To feed datetimes to the database, take the datetime stored in GMT (e.g. the pv, the result of a function) and shift it to the time zone that the database expects. Going from GMT to a local time zone requires adding the offset to the GMT representation of the datetime. You can do this with the local() function:

local(pv!DATETIME_TO_INSERT_GOES_HERE,"Australia/Sydney")

2. When you retrieve a datetime from the RDBMS, you need to find the GMT equivalent of the datetime you get from it and store that in the PV for display purposes, otherwise Appian will assume the database is sending the data in GTM and will do an adjustment that will give you the wrong timestamp.

When you have a local datetime and you want to know the GMT equivalent of it, you subtract the time zone offset from that local datetime. The way you achieve this shifting (from a local time [RDBMS time zone] to the gmt equivalent) is using the gmt() function and passing the time zone representing the offset from GMT as the second parameter. So


gmt(pv!DATE_RETRIEVED_GOES_HERE,"Australia/Sydney")

Here is a screenshot explaining the insert: forum.appian.com/.../79244
Here is a screenshot explaining the select: forum.appian.com/.../79246