Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6466

CONNECT engine cannot handle dates far in the future

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0.12
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
        • 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

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                bertrandop Olivier Bertrand added a comment -

                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.

                Show
                bertrandop Olivier Bertrand added a comment - 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.
                Hide
                Permalink
                s_weisgarber Scott Weisgarber added a comment -

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

                Show
                s_weisgarber Scott Weisgarber added a comment - Thanks, Oliver. I'll look into using the workaround for now.

                  People

                  • Assignee:
                    bertrandop Olivier Bertrand
                    Reporter:
                    s_weisgarber Scott Weisgarber
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated: