【LightDB】 时间和字符串比较-MySQL兼容

mysql 日期类型

类型	取值范围	描述
datetime	‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’	带时间点，无时区概念，默认显示格式YYYY-MM-DD hh:mm:ss
timestamp	‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC	带时区，存储utc时区，显示当前时区时间，带毫秒
date	‘1000-01-01’ to ‘9999-12-31’	无时区，只存日期（不带时间点）

与字符比较

mysql中，时间和字符串比较是，会将字符转换成对应的类型进行比较。

create table test(id int, uptime datetime);
insert into test values(1,now());
select now() > concat('2014-01-02',' 00:00:00'); 
-- 等价于以下sql
select now() > cast(concat('2014-01-02',' 00:00:00') as datetime);

兼容MySQL

LightDB只有常量字符串（unknow类型）支持直接和日期比较。当字符串类型是text,varchar(n),char(n)时，不支持此行为。为方便用户迁移，23.4版本支持日期类型和数值类型的比较。

lightdb@mysql=# create table test(id int, uptime datetime);
CREATE TABLE
lightdb@mysql=# insert into test values(1,now());
INSERT 0 1
lightdb@mysql=# select now() > concat('2014-01-02',' 00:00:00'); 
 ?column? 
----------
 t
(1 row)

create table t1(co1 text, co2 varchar);
insert into t1 values('2023-10-12 21:40:00', '2023-10-12 21:40:00');
select * from t1 where now() > co1;
         co1         |         co2         
---------------------+---------------------
 2023-10-12 21:40:00 | 2023-10-12 21:40:00
(1 row)

select * from t1 where now()::timestamp with time zone > co1;
         co1         |         co2         
---------------------+---------------------
 2023-10-12 21:40:00 | 2023-10-12 21:40:00
(1 row)

select * from t1 where now()::date > co1;
         co1         |         co2         
---------------------+---------------------
 2023-10-12 21:40:00 | 2023-10-12 21:40:00
(1 row)

SELECT   
    case co1 
        WHEN (now() < '2020-01-01')::text THEN 'update'  
        ELSE 'insert'  
    END AS res  
FROM t1;
  res   
--------
 insert
(1 row)

SELECT   
    case co1 
        WHEN (now() < co2)::text THEN 'update'  
        ELSE 'insert'  
    END AS res  
FROM t1;
  res   
--------
 insert
(1 row)

SELECT   
    case co1 
        WHEN (now()::timestamp with time zone < co2)::text THEN 'update'  
        ELSE 'insert'  
    END AS res  
FROM t1;
  res   
--------
 insert
(1 row)

SELECT   
    case co1 
        WHEN (now()::date < co2)::text THEN 'update'  
        ELSE 'insert'  
    END AS res  
FROM t1;
  res   
--------
 insert
(1 row)

drop table t1;

此外，date,time,timestamp,timestamp都支持和字符串比较。