Time conversion issue

scotte over 9 years ago

We have a field added to Oracle table as a Date type. When I select this field using the Query Database it displays the field 1 day less than how it is stored in database. For example; on the table it is "11-NOV-00", when I select it displays in variable as "11/10/2000 7:00PM", which is 1 day less. Also the date inserted into the table is correct, but when retrieve it is wrong. Does anyone know how this is happening? Also, the data type of the field is "Date Time" in the smart service properties output data tab. You can't change this... it gets that type from the database. Thanks!...

Discussion posts and replies are publicly visible

0 Mike Schmitt
Certified Lead Developer
over 9 years ago

It sounds like you need to account for conversion from UTC to your default time zone.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Aleksi White
Appian Employee
over 9 years ago

Please refer to this documentation:
forum.appian.com/.../Defining_a_Custom_Data_Type.html

Specifically the table of type mappings. Since Appian is using a datetime, does your XSD use a dateTime type and does the database use a datetime (for MySQL and MS SQL) or timestamp (for Oracle and DB2) type?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 scotte over 9 years ago

Yes, the xsd uses a dateTime. If I don't then I can't map the output from the Oracle date field I'm selecting. Yes, it's Oracle and is datetime. Thanks
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jason Ruvinsky
Certified Senior Developer
over 9 years ago

It sounds to me like you want to set your XSD to map this to a date, rather than datetime, field so it is consistent with how you are storing it in your DB. If you convert a date into a datetime, midnight GMT is the default time added, and when displayed in your local timezone this adjusts the date, which may cause unanticipated results if all you wanted was a date, rather than a timestamp.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jason Ruvinsky
Certified Senior Developer
over 9 years ago

Check out the "Primitive Data Types" table in the link that Aleksi sent. (there doesn't appear to be a way to link right to that table).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 scotte over 9 years ago

I agree but if I set the XSD to a date, then I can't map the return field from the database in the Select because it will only map to a Date Time type defined field. Even though its stored in the Oracle table as a date only.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 scotte over 9 years ago

mschmitt do you have a example of conversion from UTC to my eastern time zone? Thanks
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jason Ruvinsky
Certified Senior Developer
over 9 years ago

Are you using a Select in a Query RDBMS node? Have you tried using a query entity, or a query rule instead to get your data?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 scotte over 9 years ago

I'm using the Query Database smart service. I have not tried the entity or rule.
There is something odd about all this.... I'm just doing a simple Select retrieving a date column from a table, but Appian is subtracting a day or some about of time to make it appear 1 day less than what is stored in the table.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Mike Schmitt
Certified Lead Developer
over 9 years ago

In addition to what others are suggesting, you might want to get familiar with the local() function and similar. For instance on my project we found it helpful to create a global "toLocalDate" rule which takes a datetime (presumably from the UTC-oriented DB entry) and returns a plain date, accounting for the timezone conversion. That function contains this snippet of code (I also recommend you check the RI for null values first):

todate(
local( ri!dateTime, timezoneid() )
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel