Scheduler to refresh Specimen additional details has failed

Error: MysqlDataTruncation:Data truncation: Incorrect datetime value: '2105-01-09 00:00:00' for column 'collection_time' at row 1

Reason: This job may fail due to incorrect value for collection/received/frozen time value. The issue occurs because prior to v6.3 versions the collection_time is stored into the timestamp datatype. Range of timestamp is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

To fix the issue, instead of storing data in timestamp user needs to change the datatype to ‘datetime’ in the stored procedure.

Note: This is fixed within the code from OpenSpecimen v6.3.

To resolve the issue from backend:

Drop the existing procedure and re-create it again as follows.

DROP PROCEDURE create_specimen_stats_table;

DELIMITER $$
  create procedure create_specimen_stats_table()
  begin
   drop table if exists tmp_specimen_stats;

   create table tmp_specimen_stats (
     specimen_id bigint(19) not null,
     collector_id bigint(19) default null,
     collection_procedure varchar(50) default null,
     collection_container varchar(50) default null,
     collection_time datetime default null,
     receiver_id bigint(19) default null,
     received_quality varchar(255) default null,
     received_time datetime null default null,
     frozen_time datetime null default null,
     processing_time bigint(19) default null,
     ischemia_time bigint(19) default null,
     aliquot_seq bigint(19) default null
   );
   end$$
DELIMITER ;
commit;

Once the procedure is created. Run the job again. This time a job will run successfully.