Dear All,
After upgrading my tracker instance from 2.38 to 2.40 I noticed that whenever analytics is run the analytics fails during “populating analytics tables event”. Processing: Screenshot 2024-04-19 at 14.06.49.png…
analytic_error.docx (21.3 KB)
ERROR: date/time field value out of range: "0000-11-13"; nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-11-13"
one of your event has an “bad/empty/zero” year or one the data value with date is “out of bound” ?
1 Like
Is there any query to identify the value which is out of range? Can I delete that value using SQL query so that it could allow analytics to run?
We had a similar issue sometime back, but this issue I belive was resolved in the later patches of v40. Are you using the the latest release of v40?
But this was the temporal method used to identify the dates: select psi.uid, psi.code, psi.duedate from programstageinstance psi where (EXTRACT(year from psi.duedate) < 200 you could modify it further. You can then use an update statement to correct the dates if thats a safe option.
Dear Muyunda,
I tried to identify the date using your command but it came up empty:
SELECT psi.uid, psi.code, psi.duedate
FROM programstageinstance psi
WHERE EXTRACT(YEAR FROM psi.duedate) < 200;
uid | code | duedate
-----±-----±--------
(0 rows)
So in your case the date could be in the executiondate column, so try something like this:
select psi.uid, psi.code, psi.duedate, executiondate from programstageinstance psi where
(EXTRACT(year from psi.duedate) < 1 or EXTRACT(year from psi.executiondate) < 1);
The output is still blank:
uid | code | duedate | executiondate
-----±-----±--------±--------------
(0 rows)
I adjusted the previous query again now I have the output,
programstage.xls (818 KB)
attached is the output of the result. Don’t you think this issue is not at the program stage:
SELECT psi.uid, psi.code, psi.duedate
FROM programstageinstance psi
WHERE EXTRACT(YEAR FROM psi.duedate) < 2024;
insert into analytics_event_temp_lhvh4kgwzbo_2024 (“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,ounamehierarchy,“sHKu3lUy5cK”,“nkHn29jMdUq”,“tWCjhmvVyZq”,“s8ISObDabGS”,“daily”,“weekly”,“weeklywednesday”,“weeklythursday”,“weeklysaturday”,“weeklysunday”,“biweekly”,“monthly”,“bimonthly”,“quarterly”,“quarterlynov”,“sixmonthly”,“sixmonthlyapril”,“sixmonthlynov”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“financialnov”,“BJyB90wSTwO”,“LtdDosV3zc4”,“CDPU7h6zkt3”,“bLg5L8NPNC6”,“l3aS4xRB6kx”,“Gw2ZnayKBPo”,“Gagy6NWpON6”,“grcASD5Wiov”,“anI8j1CJNjm”,“vThhF6nR2dJ”,“GlxDP46zW6v”,“ka7DeCp2I6w”,“BE9rxtF5XjE”,“rwO1u5aIZQP”,“H83iP4mlMbY”,“jFkmgjHA8e9”,“E6MBmrBR5O9”,“QVcLOQC9kqb”,“MdLGFujlOMo”,“zU7GUG2ozeW”,“mK08PRDY1iJ”,“IFJVfQ1yXDO”,“y4wwmEiTkWd”,“lhzv5apajGm”,“DS5XNmqUaFI”,“deZzP2kDxI7”,“LG84CIRv8RZ”,“dEaZrq0VdD1”,“qTZcH6GwYF3”,“VD45EAd4U1h”,“KfSHOAAp3vk”,“FcfE8R6aTXU”,“obkFULI5x42”,“zuaMocjpl2c”,“n0xS9UzF8eh”,“oi1hJL7wUtY”,“b1wK0m8mHfC”,“Q0VYNPvbvYU”,“R5NgX7kNPMN”,“FWLO793nIoG”,“cT1yepwNAhm”,“CN3hAGYHiMq”,“hZ3LYwCikjc”,“dm2sECZPxMp”,“X2MzZ4mHXu6”,“EgOYAiAK57a”,“YkaQ86MDoLD”,“AXWhFHipZAS_geom”,“AXWhFHipZAS_name”,“AXWhFHipZAS”,“WO7Ii2FzOyD”,“mw9Fo8yjF3s”,“xreCINAPMKD”,“cALtJMLWGBj”,“yYZiRr9Ohzw”,“rwtxSlai7Hq”,“cxd06uxRbPz”,“Vsvl45wck3i”,“EmOB5xCUWSS”,“z3JPJhNdfP6”,“bYJoJeis8fb”,“wbfhI95Wgjz”,“FwW4I11AMBy”,“PAhL0YGnGgg”,“K9juN8tEfdN”,“y1ohbfqdFet”,“QjcXqvw8bDR”,“Cc3Hw2elY4j”,“slngCJuT1Wg”,“W1mkwdAcMmk”,“L0lneavczr4”,“Ouh33LgfAXy”,“kE6uARw1I5b”,“uZq9tLpzehG”,“gBdhGkLOieK”,“jtNRru5Ni8F”,“Ao1DBSxRDqO”,“Dk06hmdgnNA”,“Hn6QP3m3KvO”,“I7L1jfOkDgt”,“ftAIIczkonD”,“nmShSEafggj”,“RSjTfwlmC4Q”,“yVyA4tLOehx”,“NDHjE3RNzCZ”,“lEe9SD8lgy0”,“L9ioNLd1TKz”,“MvqG04RNxJ6”,“onwHBme4ERB”,“JRMtbIiJMs0”,“wCMGQubR3AB”,“BKxPTVSd7rj”,“XRtl9lbxA4V”,“aNflPiAsDkm”,“bujw8anlXGm”,“QXk6mkhHddU”,“LW14WvFZQv7”,“Sisk2Tbn5lZ”,“aTMSvkqwiXP”,“pDE0n1hQI9R”,“rKgQp6PqiUC”,“BhjnsKKcEkQ”,“ygY82G5Vzed”,“NdxLfcYsFn7”,“tMUi3ujjeMi”,“KSr2yTdu1AI”,“b5LGBZiX1Xs”,“urQhsUu7xnw”,“JY9oCrH7CPQ”,“B9SC2oB2KCA”,“bZpJwV97Sca”,“EhEPmB7n31b”,“z3wGrlnviiT”,“ciCR6BBvIT4”,“dEaOiSZ2uFB”,“d05HXrUOiON”,“IkrDiUPxCPK”,“TU48jdmcKMZ”,“psi”,“pi”,“ps”,“ao”,“enrollmentdate”,“incidentdate”,“executiondate”,“duedate”,“completeddate”,“created”,“lastupdated”,“storedby”,“createdbyusername”,“createdbyname”,“createdbylastname”,“createdbydisplayname”,“lastupdatedbyusername”,“lastupdatedbyname”,“lastupdatedbylastname”,“lastupdatedbydisplayname”,“pistatus”,“psistatus”,“psigeometry”,“longitude”,“latitude”,“ou”,“ouname”,“oucode”,“oulevel”,“ougeometry”,“pigeometry”,“registrationou”,“enrollmentou”,“tei”,“teigeometry”) select ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,concat_ws(’ / ',ous.namelevel1,ous.namelevel2,ous.namelevel3,ous.namelevel4) as ounamehierarchy,ougs.“sHKu3lUy5cK”,ougs.“nkHn29jMdUq”,ougs.“tWCjhmvVyZq”,ougs.“s8ISObDabGS”,dps.“daily”,dps.“weekly”,dps.“weeklywednesday”,dps.“weeklythursday”,dps.“weeklysaturday”,dps.“weeklysunday”,dps.“biweekly”,dps.“monthly”,dps.“bimonthly”,dps.“quarterly”,dps.“quarterlynov”,dps.“sixmonthly”,dps.“sixmonthlyapril”,dps.“sixmonthlynov”,dps.“yearly”,dps.“financialapril”,dps.“financialjuly”,dps.“financialoct”,dps.“financialnov”,(select eventdatavalues #>> ‘{BJyB90wSTwO, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “BJyB90wSTwO”,(select eventdatavalues #>> ‘{LtdDosV3zc4, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “LtdDosV3zc4”,(select cast(eventdatavalues #>> ‘{CDPU7h6zkt3, value}’ as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{CDPU7h6zkt3,value}’ ~* ‘^(-?[0-9]+)(.[0-9]+)?$’) as “CDPU7h6zkt3”,(select case when eventdatavalues #>> ‘{bLg5L8NPNC6, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{bLg5L8NPNC6, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “bLg5L8NPNC6”,(select case when eventdatavalues #>> ‘{l3aS4xRB6kx, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{l3aS4xRB6kx, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “l3aS4xRB6kx”,(select cast(eventdatavalues #>> ‘{Gw2ZnayKBPo, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{Gw2ZnayKBPo,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “Gw2ZnayKBPo”,(select eventdatavalues #>> ‘{Gagy6NWpON6, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Gagy6NWpON6”,(select eventdatavalues #>> ‘{grcASD5Wiov, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “grcASD5Wiov”,(select cast(eventdatavalues #>> ‘{anI8j1CJNjm, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{anI8j1CJNjm,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “anI8j1CJNjm”,(select eventdatavalues #>> ‘{vThhF6nR2dJ, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “vThhF6nR2dJ”,(select eventdatavalues #>> ‘{GlxDP46zW6v, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “GlxDP46zW6v”,(select cast(eventdatavalues #>> ‘{ka7DeCp2I6w, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{ka7DeCp2I6w,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “ka7DeCp2I6w”,(select cast(eventdatavalues #>> ‘{BE9rxtF5XjE, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{BE9rxtF5XjE,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “BE9rxtF5XjE”,(select case when eventdatavalues #>> ‘{rwO1u5aIZQP, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{rwO1u5aIZQP, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “rwO1u5aIZQP”,(select eventdatavalues #>> ‘{H83iP4mlMbY, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “H83iP4mlMbY”,(select eventdatavalues #>> ‘{jFkmgjHA8e9, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “jFkmgjHA8e9”,(select cast(eventdatavalues #>> ‘{E6MBmrBR5O9, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{E6MBmrBR5O9,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “E6MBmrBR5O9”,(select eventdatavalues #>> ‘{QVcLOQC9kqb, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “QVcLOQC9kqb”,(select cast(eventdatavalues #>> ‘{MdLGFujlOMo, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{MdLGFujlOMo,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “MdLGFujlOMo”,(select eventdatavalues #>> ‘{zU7GUG2ozeW, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “zU7GUG2ozeW”,(select eventdatavalues #>> ‘{mK08PRDY1iJ, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “mK08PRDY1iJ”,(select cast(eventdatavalues #>> ‘{IFJVfQ1yXDO, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{IFJVfQ1yXDO,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “IFJVfQ1yXDO”,(select cast(eventdatavalues #>> ‘{y4wwmEiTkWd, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{y4wwmEiTkWd,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “y4wwmEiTkWd”,(select cast(eventdatavalues #>> ‘{lhzv5apajGm, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{lhzv5apajGm,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “lhzv5apajGm”,(select eventdatavalues #>> ‘{DS5XNmqUaFI, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “DS5XNmqUaFI”,(select eventdatavalues #>> ‘{deZzP2kDxI7, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “deZzP2kDxI7”,(select eventdatavalues #>> ‘{LG84CIRv8RZ, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “LG84CIRv8RZ”,(select eventdatavalues #>> ‘{dEaZrq0VdD1, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “dEaZrq0VdD1”,(select eventdatavalues #>> ‘{qTZcH6GwYF3, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “qTZcH6GwYF3”,(select eventdatavalues #>> ‘{VD45EAd4U1h, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “VD45EAd4U1h”,(select cast(eventdatavalues #>> ‘{KfSHOAAp3vk, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{KfSHOAAp3vk,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “KfSHOAAp3vk”,(select cast(eventdatavalues #>> ‘{FcfE8R6aTXU, value}’ as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{FcfE8R6aTXU,value}’ ~* ‘^(-?[0-9]+)(.[0-9]+)?$’) as “FcfE8R6aTXU”,(select eventdatavalues #>> ‘{obkFULI5x42, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “obkFULI5x42”,(select cast(eventdatavalues #>> ‘{zuaMocjpl2c, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{zuaMocjpl2c,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “zuaMocjpl2c”,(select eventdatavalues #>> ‘{n0xS9UzF8eh, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “n0xS9UzF8eh”,(select eventdatavalues #>> ‘{oi1hJL7wUtY, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “oi1hJL7wUtY”,(select eventdatavalues #>> ‘{b1wK0m8mHfC, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “b1wK0m8mHfC”,(select eventdatavalues #>> ‘{Q0VYNPvbvYU, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Q0VYNPvbvYU”,(select eventdatavalues #>> ‘{R5NgX7kNPMN, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “R5NgX7kNPMN”,(select cast(eventdatavalues #>> ‘{FWLO793nIoG, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{FWLO793nIoG,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “FWLO793nIoG”,(select cast(eventdatavalues #>> ‘{cT1yepwNAhm, value}’ as double precision) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{cT1yepwNAhm,value}’ ~* ‘^(-?[0-9]+)(.[0-9]+)?$’) as “cT1yepwNAhm”,(select eventdatavalues #>> ‘{CN3hAGYHiMq, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “CN3hAGYHiMq”,(select eventdatavalues #>> ‘{hZ3LYwCikjc, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “hZ3LYwCikjc”,(select eventdatavalues #>> ‘{dm2sECZPxMp, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “dm2sECZPxMp”,(select cast(eventdatavalues #>> ‘{X2MzZ4mHXu6, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{X2MzZ4mHXu6,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “X2MzZ4mHXu6”,(select eventdatavalues #>> ‘{EgOYAiAK57a, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “EgOYAiAK57a”,(select eventdatavalues #>> ‘{YkaQ86MDoLD, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “YkaQ86MDoLD”,(select ou.geometry from organisationunit ou where ou.uid = (select eventdatavalues #>> ‘{AXWhFHipZAS, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid )) as “AXWhFHipZAS”,(select ou.name from organisationunit ou where ou.uid = (select eventdatavalues #>> ‘{AXWhFHipZAS, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid )) as “AXWhFHipZAS”,(select ou.uid from organisationunit ou where ou.uid = (select eventdatavalues #>> ‘{AXWhFHipZAS, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid )) as “AXWhFHipZAS”,(select case when eventdatavalues #>> ‘{WO7Ii2FzOyD, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{WO7Ii2FzOyD, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “WO7Ii2FzOyD”,(select case when eventdatavalues #>> ‘{mw9Fo8yjF3s, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{mw9Fo8yjF3s, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “mw9Fo8yjF3s”,(select eventdatavalues #>> ‘{xreCINAPMKD, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “xreCINAPMKD”,(select eventdatavalues #>> ‘{cALtJMLWGBj, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “cALtJMLWGBj”,(select eventdatavalues #>> ‘{yYZiRr9Ohzw, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “yYZiRr9Ohzw”,(select cast(eventdatavalues #>> ‘{rwtxSlai7Hq, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{rwtxSlai7Hq,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “rwtxSlai7Hq”,(select case when eventdatavalues #>> ‘{cxd06uxRbPz, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{cxd06uxRbPz, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “cxd06uxRbPz”,(select eventdatavalues #>> ‘{Vsvl45wck3i, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Vsvl45wck3i”,(select eventdatavalues #>> ‘{EmOB5xCUWSS, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “EmOB5xCUWSS”,(select eventdatavalues #>> ‘{z3JPJhNdfP6, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “z3JPJhNdfP6”,(select eventdatavalues #>> ‘{bYJoJeis8fb, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “bYJoJeis8fb”,(select cast(eventdatavalues #>> ‘{wbfhI95Wgjz, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{wbfhI95Wgjz,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “wbfhI95Wgjz”,(select eventdatavalues #>> ‘{FwW4I11AMBy, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “FwW4I11AMBy”,(select eventdatavalues #>> ‘{PAhL0YGnGgg, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “PAhL0YGnGgg”,(select cast(eventdatavalues #>> ‘{K9juN8tEfdN, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{K9juN8tEfdN,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “K9juN8tEfdN”,(select eventdatavalues #>> ‘{y1ohbfqdFet, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “y1ohbfqdFet”,(select eventdatavalues #>> ‘{QjcXqvw8bDR, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “QjcXqvw8bDR”,(select eventdatavalues #>> ‘{Cc3Hw2elY4j, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Cc3Hw2elY4j”,(select eventdatavalues #>> ‘{slngCJuT1Wg, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “slngCJuT1Wg”,(select eventdatavalues #>> ‘{W1mkwdAcMmk, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “W1mkwdAcMmk”,(select case when eventdatavalues #>> ‘{L0lneavczr4, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{L0lneavczr4, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “L0lneavczr4”,(select eventdatavalues #>> ‘{Ouh33LgfAXy, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Ouh33LgfAXy”,(select eventdatavalues #>> ‘{kE6uARw1I5b, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “kE6uARw1I5b”,(select eventdatavalues #>> ‘{uZq9tLpzehG, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “uZq9tLpzehG”,(select eventdatavalues #>> ‘{gBdhGkLOieK, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “gBdhGkLOieK”,(select cast(eventdatavalues #>> ‘{jtNRru5Ni8F, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{jtNRru5Ni8F,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “jtNRru5Ni8F”,(select eventdatavalues #>> ‘{Ao1DBSxRDqO, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Ao1DBSxRDqO”,(select eventdatavalues #>> ‘{Dk06hmdgnNA, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Dk06hmdgnNA”,(select eventdatavalues #>> ‘{Hn6QP3m3KvO, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Hn6QP3m3KvO”,(select eventdatavalues #>> ‘{I7L1jfOkDgt, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “I7L1jfOkDgt”,(select eventdatavalues #>> ‘{ftAIIczkonD, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “ftAIIczkonD”,(select cast(eventdatavalues #>> ‘{nmShSEafggj, value}’ as double precision) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{nmShSEafggj,value}’ ~* ‘^(-?[0-9]+)(.[0-9]+)?$’) as “nmShSEafggj”,(select eventdatavalues #>> ‘{RSjTfwlmC4Q, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “RSjTfwlmC4Q”,(select eventdatavalues #>> ‘{yVyA4tLOehx, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “yVyA4tLOehx”,(select eventdatavalues #>> ‘{NDHjE3RNzCZ, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “NDHjE3RNzCZ”,(select eventdatavalues #>> ‘{lEe9SD8lgy0, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “lEe9SD8lgy0”,(select cast(eventdatavalues #>> ‘{L9ioNLd1TKz, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{L9ioNLd1TKz,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “L9ioNLd1TKz”,(select eventdatavalues #>> ‘{MvqG04RNxJ6, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “MvqG04RNxJ6”,(select cast(eventdatavalues #>> ‘{onwHBme4ERB, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{onwHBme4ERB,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “onwHBme4ERB”,(select cast(eventdatavalues #>> ‘{JRMtbIiJMs0, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{JRMtbIiJMs0,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “JRMtbIiJMs0”,(select eventdatavalues #>> ‘{wCMGQubR3AB, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “wCMGQubR3AB”,(select case when eventdatavalues #>> ‘{BKxPTVSd7rj, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{BKxPTVSd7rj, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “BKxPTVSd7rj”,(select cast(eventdatavalues #>> ‘{XRtl9lbxA4V, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{XRtl9lbxA4V,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “XRtl9lbxA4V”,(select case when eventdatavalues #>> ‘{aNflPiAsDkm, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{aNflPiAsDkm, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “aNflPiAsDkm”,(select cast(eventdatavalues #>> ‘{bujw8anlXGm, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{bujw8anlXGm,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “bujw8anlXGm”,(select cast(eventdatavalues #>> ‘{QXk6mkhHddU, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{QXk6mkhHddU,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “QXk6mkhHddU”,(select eventdatavalues #>> ‘{LW14WvFZQv7, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “LW14WvFZQv7”,(select eventdatavalues #>> ‘{Sisk2Tbn5lZ, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “Sisk2Tbn5lZ”,(select eventdatavalues #>> ‘{aTMSvkqwiXP, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “aTMSvkqwiXP”,(select case when eventdatavalues #>> ‘{pDE0n1hQI9R, value}’ = ‘true’ then 1 when eventdatavalues #>> ‘{pDE0n1hQI9R, value}’ = ‘false’ then 0 else null end from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “pDE0n1hQI9R”,(select cast(eventdatavalues #>> ‘{rKgQp6PqiUC, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{rKgQp6PqiUC,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “rKgQp6PqiUC”,(select cast(eventdatavalues #>> ‘{BhjnsKKcEkQ, value}’ as timestamp) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> ‘{BhjnsKKcEkQ,value}’ ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “BhjnsKKcEkQ”,(select eventdatavalues #>> ‘{ygY82G5Vzed, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “ygY82G5Vzed”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1806) as “NdxLfcYsFn7”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=41264) as “tMUi3ujjeMi”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1934) as “KSr2yTdu1AI”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=36568) as “b5LGBZiX1Xs”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1808) as “urQhsUu7xnw”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1809) as “JY9oCrH7CPQ”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1811) as “B9SC2oB2KCA”,(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1813 and value ~* ‘^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$’) as “bZpJwV97Sca”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1814) as “EhEPmB7n31b”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1810) as “z3wGrlnviiT”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1878) as “ciCR6BBvIT4”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=42130) as “dEaOiSZ2uFB”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=39776) as “d05HXrUOiON”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=36889) as “IkrDiUPxCPK”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=36567) as “TU48jdmcKMZ”,psi.uid,pi.uid,ps.uid,ao.uid,pi.enrollmentdate,pi.incidentdate,psi.executiondate,psi.duedate,psi.completeddate,CASE WHEN psi.createdatclient IS NOT NULL THEN psi.createdatclient ELSE psi.created END,CASE WHEN psi.lastupdatedatclient IS NOT NULL THEN psi.lastupdatedatclient ELSE psi.lastupdated END,psi.storedby,psi.createdbyuserinfo ->> ‘username’ as createdbyusername,psi.createdbyuserinfo ->> ‘firstName’ as createdbyname,psi.createdbyuserinfo ->> ‘surname’ as createdbylastname,case when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then null when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) <> ‘’ then trim(psi.createdbyuserinfo ->> ‘username’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.createdbyuserinfo ->> ‘firstName’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.createdbyuserinfo ->> ‘surname’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then concat(trim(psi.createdbyuserinfo ->> ‘surname’), ‘, ‘, trim(psi.createdbyuserinfo ->> ‘firstName’)) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.createdbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.createdbyuserinfo ->> ‘username’), ‘)’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.createdbyuserinfo ->> ‘surname’), ’ (’, trim(psi.createdbyuserinfo ->> ‘username’), ‘)’) else concat(trim(psi.createdbyuserinfo ->> ‘surname’), ', ‘, trim(psi.createdbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.createdbyuserinfo ->> ‘username’), ‘)’) end as createdbydisplayname,psi.lastupdatedbyuserinfo ->> ‘username’ as lastupdatedbyusername,psi.lastupdatedbyuserinfo ->> ‘firstName’ as lastupdatedbyname,psi.lastupdatedbyuserinfo ->> ‘surname’ as lastupdatedbylastname,case when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then null when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) <> ‘’ then trim(psi.lastupdatedbyuserinfo ->> ‘username’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.lastupdatedbyuserinfo ->> ‘firstName’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.lastupdatedbyuserinfo ->> ‘surname’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then concat(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘, ‘, trim(psi.lastupdatedbyuserinfo ->> ‘firstName’)) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘)’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ’ (’, trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘)’) else concat(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ', ‘, trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘)’) end as lastupdatedbydisplayname,pi.status,psi.status,psi.geometry,CASE WHEN ‘POINT’ = GeometryType(psi.geometry) THEN ST_X(psi.geometry) ELSE null END,CASE WHEN ‘POINT’ = GeometryType(psi.geometry) THEN ST_Y(psi.geometry) ELSE null
END,ou.uid,ou.name,ou.code,ous.level,ou.geometry,pi.geometry,coalesce(registrationou.uid,ou.uid),coalesce(enrollmentou.uid,ou.uid),tei.uid,tei.geometry from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid inner join programstage ps on psi.programstageid=ps.programstageid inner join program pr on pi.programid=pr.programid and pi.deleted is false inner join categoryoptioncombo ao on psi.attributeoptioncomboid=ao.categoryoptioncomboid left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false left join organisationunit registrationou on tei.organisationunitid=registrationou.organisationunitid inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid left join _organisationunitgroupsetstructure ougs on psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc(‘month’, CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) as date)=ougs.startdate or ougs.startdate is null) left join organisationunit enrollmentou on pi.organisationunitid=enrollmentou.organisationunitid inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END as date)=dps.dateperiod where psi.lastupdated < ‘2024-04-24T15:02:27’ and (CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) >= ‘2024-01-01T00:00:00’ and (CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) < ‘2025-01-01T00:00:00’ and pr.programid=36888 and psi.organisationunitid is not null and (CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) is not null and dps.year >= 1975 and dps.year <= 2049 and psi.status in (‘COMPLETED’,‘ACTIVE’,‘SCHEDULE’)and psi.deleted is false ]; ERROR: date/time field value out of range: “0000-03-25”; nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: “0000-03-25”
this is the error I am getting and it is very confusing and long.
Try to extract the executiondates as well and check. Since you have the actual date causing the error, you could also just look for the specific record that has either duedate or executiondate = ‘0000-03-25’.
This is really interesting Muyunda and I don’t know how much you can help; when I did this query for the specific date:
SELECT psi.uid, psi.code, psi.duedate, psi.executiondate FROM programstageinstance psi WHERE psi.executiondate = ‘0000-03-25’;
I am getting this error:
ERROR: date/time field value out of range: “0000-03-25”
LINE 1: …rogramstageinstance psi WHERE psi.executiondate = '0000-03-2…
And if I do this query:
**SELECT psi.uid, psi.code, psi.duedate, psi.executiondate **
**FROM programstageinstance psi **
WHERE EXTRACT(YEAR FROM psi.duedate) < 1 OR EXTRACT(YEAR FROM psi.executiondate) < 1;
I am getting this output:
uid | code | duedate | executiondate
-----±-----±--------±--------------
(0 rows)