Database showing offset value of date time variable

mikec over 7 years ago

I have a date time issue. In my interface a user can select a date and time. Lets use the following example: Start Date 08/04/2016 time 09:00 AM End Date is 08/30/2016 11:59 PM. When viewed in the pv this time is shown. When viewed in the DB once saved it is adding 4 hours so in the DB its 13:00 and 03:59. The calendar and internationalization are both set to UTC-05:00 Eastern Time (America/New_York. How can I get the values to save in the DB as what is entered in the interface form?

Also, from the form we create a docx file that is downloadable. In these time fields they also show the +4 hours time. How do I get it to show the actually selected time.

Thanks.

Discussion posts and replies are publicly visible

+1 PhilB
Certified Lead Developer
over 7 years ago

Are you on Appian cloud? If so there's some detail on this from the latest release here: forum.appian.com/.../Appian_Release_Notes.html

How are you retrieving the dateTimes from the database? Often the local() function is useful for getting the stored datetimes correct. As I understand it, the database stores in GMT but Appian should convert to and from the system locality for you, and any further user-orientated display should use local() function.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Reject Answer

Cancel
0 Sandeep Deshmukh over 7 years ago

@Mike, Same issue faced by me as well while storing Date and Time into DB, but while retrieving these values from dB I received the correct Date and Time only. Could you please try query Entity for fetching date and time in your Interface first.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sanjays0006 over 7 years ago

Even we are facing this issue. Appian by default saves the values into the backend with GMT timezone.
While retrieving the data, we are using the local() function to display the time in local timezone.
When exporting the date time field into a document, we should tweak the data by reducing 4 hrs and then export it to the document.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Alok Patro over 7 years ago

Hi Mike, Whatever data you input in the interface, the same you will get when you retrieve it through the interface. Sometimes, due to some timezone setup we get different values in the DB .

Use the below query to check the timezone of the database :

SELECT @@system_time_zone
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ankitab918 over 7 years ago

I am also facing similar problem, however in my case i am not saving it in the DB yet. Suppose, in the date time field, i am entering 8/24/2016 5:00 PM, as soon as I click outside the field, it's deducting 4 hours and the date time becomes 8/24/2016 1:00 PM. Any suggestion how to handle this behavior?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 narasimhaadityac
A Score Level 2
over 7 years ago

Hi Mike and Ankitab, Please see if you can use the datevalue() and timevalue() functions and then text() function to get them to the desired format.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Alok Patro over 7 years ago

Hi ankitab918, can you share the code to know the issue more elaborately
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ankitab918 over 7 years ago

load(
local!selectedRecord,
<other codes>
a!dateTimeField(
label:"Date : ",
labelPosition:"ADJACENT",
value:/*if(local!selectedRecord.ID="",gmt(*/local!selectedRecord.Date/*),rule!ACP_ConvertTextDateTimeToDate(local!selectedRecord.Date))*/,
saveInto:/*a!save(*/local!selectedRecord.Date,/*save!value),*/
required:if(local!selectedRecord.ID="",true(),false()),
readOnly: if(local!selectedRecord.ID="",false(),true())
),
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ankitab918 over 7 years ago

@narasimhaadityac: I tried using the functions but it's giving me pink errors. Referring to the above code can you tell me how should I use those functions?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
Certified Lead Developer
over 7 years ago

That code is using a rule that you probably don't have - rule!ACP_ConvertTextDateTimeToDate - so it won't work.

If you refer to the documentation (forum.appian.com/.../SAIL_Components.html, it says: "The date and time is shown in the current user's time zone and calendar format."

Could it be that your user is set to a different timezone? Alternatively there could be a problem with that field, or with your saveInto for the field.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel