[MDEV-6466] CONNECT engine cannot handle dates far in the future Created: 2014-07-21  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.12
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Scott Weisgarber Assignee: Andrew Hutchings
Resolution: Unresolved Votes: 0
Labels: connect-engine
Environment:

EC2 instance, Amazon Linux AMI, MaraiaDB 10.0.12


 Description   

The CONNECT engine uses a 32-bit date and cannot handle dates greater than 2038-01-19: they wrap around to 1/1/1970.

Our data warehouse uses date ranging (begin and end dates) to track history, and the "current" data has an end date of 8/8/8888. Unfortunately, when trying to transfer data between instances, these dates are returned as 1/1/1970 (in the previous .11 release, they were 1/1/1969). This is preventing us from using the CONNECT engine.

create table raw_feed.bad_date (d date);
 
insert into raw_feed.bad_date values ('2038-01-20');
 
select * from raw_feed.bad_date;
 
--> 1/20/2038 12:00:00 AM
 
 
 
CREATE TABLE connectdb.bad_date_local (d date) ENGINE=CONNECT DEFAULT CHARSET=latin1 
CONNECTION='mysql://_(same instance)_/raw_feed/bad_date' table_type=mysql option_list='password=....';
 
select * from connectdb.bad_date_local;
 
 
 
--> 1/1/1970 12:00:00 AM



 Comments   
Comment by Olivier Bertrand [ 2014-07-23 ]

I think I can handle this. Indeed, converting to a 32 bit integer is useless
when a DATE_FORMAT is specified.

Meanwhile, a simple workaround is to declare the columns are CHAR using the
MariaDB standard representation in them. For instance:

create table datest (
 
id int not null,
 
dat char(10) not null,
 
tim char(8) default '00:00:00',
 
datim char(19) default '1789-08-10 14:20:30')
 
engine=connect table_type=FIX;
 
insert into datest(id,dat) values(1,'1515-04-01'),(2,'2014-07-23'),(3,'2118-11-02');
 
select * from datest;

id dat tim datim
1 1515-04-01 00:00:00 1789-08-10 14:20:30
2 2014-07-23 00:00:00 1789-08-10 14:20:30
3 2118-11-02 00:00:00 1789-08-10 14:20:30
4 2014-07-23 12:06:28 2014-07-23 12:06:28 

select id, date(datim) from datest limit 1;

id date(datim)
1 1789-08-10 

select id, dayname(dat) from datest;

id dayname(dat)
1 Thursday
2 Wednesday
3 Wednesday 

insert into datest values(4,curdate(),curtime(),now());
 
select * from datest;

id dat tim datim
1 1515-04-01 00:00:00 1789-08-10 14:20:30
2 2014-07-23 00:00:00 1789-08-10 14:20:30
3 2118-11-02 00:00:00 1789-08-10 14:20:30
4 2014-07-23 12:06:28 2014-07-23 12:06:28

Everything seem to work as if the columns were true date types.

Comment by Scott Weisgarber [ 2014-07-24 ]

Thanks, Oliver. I'll look into using the workaround for now.

Generated at Thu Feb 08 07:12:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.