Date Stored from prior to Updates to 1.8 (poss 1.7)

Submitted by Ben_Charlton on Sat, 19/09/2015 - 09:33

General developer's discussion

I cant remember when we changed the way dates are stored but I have noticed that we still have the old style dates stored in the system.

for example in the Dob column we have the old style

2010-06-03 00:00:00.0 EST

as well as the new style

2010-06-03 00:00:00.0

This poses an issue when using sql functions as we need to use a STR-DATE function before a format function.

Should we include a sql function in the update to clean the data properly so it is all in the correct format?

? Why where dates not stored in an accepted UNIX Timestamp? It would have been much more portable to do so?

Re: Date Stored from prior to Updates to 1.8 (poss 1.7)

Submitted by tanderson on Sat, 19/09/2015 - 10:05.

The migrate-1.7-to-1.8.sql script does include support for this:

#
# 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";

You should be able to run this snippet without re-running the entire migration, although perform a backup first.

Re: Date Stored from prior to Updates to 1.8 (poss 1.7)

Submitted by Ben_Charlton on Sat, 19/09/2015 - 12:18.

Bizzare we didnt have any in the act_details table

but a good 5000 entries in the entity_details needed conversion

I swore I ran the migrate script and no errors got returned.

Regards

Ben

OpenVPMS Installer and Helper

Ph: +61423044823

Email: info[at]charltonit.com[dot]au

Re: Date Stored from prior to Updates to 1.8 (poss 1.7)

Submitted by Ben_Charlton on Sat, 19/09/2015 - 12:18.

Bizzare we didnt have any in the act_details table

but a good 5000 entries in the entity_details needed conversion

I swore I ran the migrate script and no errors got returned.

Regards

Ben

OpenVPMS Installer and Helper

Ph: +61423044823

Email: info[at]charltonit.com[dot]au

Re: Date Stored from prior to Updates to 1.8 (poss 1.7)

Submitted by tgething (not verified) on Mon, 21/09/2015 - 04:32.

Ben - I ran some queries though our system and convinced myself that we got a full conversion - ie there are none of the old format entries there - in both act_details and entity_details.

Regards, Tim G