How to handle old format dates?

Submitted by Guest on Mon, 11/01/2016 - 04:49

Implementation discussion

I am writing a report for someone (MY) who has been using OpenVPMS for a long time. Specifically prior to 1.7 when the date format was switched to sql-timestamp.

Hence his system contains date data like "2012-05-04 14:00:00.0 EST" rather than the modern "2012-05-04 14:00:00.0".

A lot of the current reports are written using sql like
cast(d1.value as date) as pDOB

However, when this encounters an old format date it generate null - ie it appears that the patient has no DOB set.

Experimentation (and looking at old reports) shows that if you use sql like
d1.value as pDOB
AND set set the datatype in JasperSoft Studio as java.util.date then this can handle both old and new format dates.

What is the best way to handle this situation? Although I can tweak the report I am writing using the above trick, there are a number of standard reports in the system that use the 'cast(value as date) construct.

Should I suggest that MY use some SQL to strip the timezone information from all fields in entity_details and act_details that have type sql-timestamp?

Regards, Tim G

Re: How to handle old format dates?

Submitted by tgething (not verified) on Mon, 11/01/2016 - 05:25.

To add to the above, the only two files that appear to contain what is now sql-timestamp data are entity_details and act_details.

Hence the relevant SQL (for SQL workbench) is:

select substring(d.value,1,21),d.value, d.name from entity_details d
where d.type = 'sql-timestamp'
and length(d.value) > 24;

select substring(d.value,1,21),d.value, d.name from act_details d
where d.type = 'sql-timestamp'
and length(d.value) > 24;

SET SQL_SAFE_UPDATES=0;

update entity_details d
set d.value = substring(d.value,1,21)
where d.type = 'sql-timestamp'
and length(d.value) > 24;

update act_details d
set d.value = substring(d.value,1,21)
where d.type = 'sql-timestamp'
and length(d.value) > 24;

SET SQL_SAFE_UPDATES=1;

The first two run queries to show you want will be updated - note the use of the length selection to find data containing timezones - this is betting that using say "like '%EST' ".

Then you need to turn safe updates off, then run the two update queries and then turn safe updates on again. You can then run the first two again and they should find nothing.

Regards, Tim G

Re: How to handle old format dates?

Submitted by tanderson on Mon, 11/01/2016 - 10:02.

These should be migrated as part of the migrate-1.7-to-1.8.sql migration script.

i.e.

#
# OVPMS-1627 Migrate date details nodes to sql-timestamp
#
update act_details d
set d.value=substring(value, 1, 21),
    d.type="sql-timestamp"
where d.type = "date";

update entity_details d
set d.value=substring(value, 1, 21),
    d.type="sql-timestamp"
where d.type = "date";