I am using Appian 18.4 on prem. I am using the Log Reader plugin to read data from log files and write the data into database tables. I want for the timestamp columns to be of type Date and Time. For example, the log file site_usage.csv contains the timestamp in the format 11 Jun 2019 05:30:40 GMT. I am in need to convert that to Date and Time. I could easily create expression rule to do that. However, what happens if:
1. In the future we move to the cloud or other environment and that other environment have different date format in the logs.
2. What if Appian changes the format of the column in the future?
3. Etc.
Having said all that, what do others recommend I should do in terms of the logic to convert the text timestamp of any log file to Date and Time and bullet proof for any future changes?
Discussion posts and replies are publicly visible
So, bullet proof is a hard goal to achieve :-)
However, if you just always store the date/time in a char field in the DB, you can create DB functions or Appian expression rules to apply formatting based on what text comes back. Doesn't make it bullet proof, but makes it more 'adaptable' to storing any value and then formatting to display as desired. Just a thought....
What if I want to query: give me the log rows from the past 6 months? How would I do that if the table timestamp columns are text?
Create a view and do a to_date on the column in the where.
Thanks ChristineH I am interested to know what others think as well.
parseDate() from Date and Time Utilities should help you. It supports many input date formats and returns the output as Date and Time.
I hope this function will future proof your solution as supported list of input date formats is comprehensive not just limited to timestamps but other possible date formats. Check the plug-in documentation.
See below screenshot for function description, input, and output.
You can apply the parseDate() function on timestamp values before inserting data into database.
I will give this a try and see if it works.My only concern is that it says it is only supported until Appian 17.3. Any thoughts on that?
Also, I tried to format "12 Jun 2019 13:55:43 GMT" using parsedate() but it didn't work. I tried without the GMT and it worked. This presents another challenge now because I won't know if the particular log file has timezone or not in the timestamp column.
Hi,
We are having the same issue. Is there a way around this?