---- b compatibility case
set dolphin.b_compatibility_mode = off;
drop database if exists b_datetime_func_test3;
NOTICE: database "b_datetime_func_test3" does not exist, skipping
create database b_datetime_func_test3 encoding 'UTF-8' lc_collate 'C' lc_ctype 'C' dbcompatibility 'B';
\c b_datetime_func_test3
set datestyle = 'ISO,ymd';
set time zone "Asia/Shanghai";
create table test(funcname text, result text);
-- test datediff
-- 严格模式或者非严格模式都有值
-- 功能
insert into test values('datediff(''2021-11-12'', ''2021-11-13'')', datediff('2021-11-12','2021-11-13'));
insert into test values('datediff(''2021-01-01'', ''2021-12-31'')', datediff('2021-01-01','2021-12-31'));
insert into test values('datediff(''2021-12-31'', ''2022-01-01'')', datediff('2021-12-31','2022-01-01'));
insert into test values('datediff(''2021-12-31 12:12:12'', ''2022-01-01 10:10:10'')', datediff('2021-12-31 12:12:12','2022-01-01 10:10:10'));
-- 边界
insert into test values('datediff(''0000-01-01'', ''9999-12-31'')', datediff('0000-01-01', '9999-12-31'));
insert into test values('datediff(''9999-12-31'', ''0000-01-01'')', datediff('9999-12-31', '0000-01-01'));
-- 类型
insert into test values('datediff(date''2021-11-12'', date''2021-11-13'')', datediff(date'2021-11-13', date'2021-11-13'));
insert into test values('datediff(cast(''0021-12-31 12:12:12'' as date), cast(''2022-01-01 10:10:10'' as date))', datediff(cast('0021-12-31 12:12:12' as date), cast('2022-01-01 10:10:10' as date)));
insert into test values('datediff(cast(''2021-12-31 12:12:12'' as datetime), cast(''2022-01-01 10:10:10'' as datetime))', datediff(cast('2021-12-31 12:12:12' as datetime), cast('2022-01-01 10:10:10' as datetime)));
insert into test values('datediff(20211112, 20211113)', datediff(20211112, 20211113));
insert into test values('datediff(time''10:10:10'', ''2022-11-30'')', datediff(time'10:10:10', '2022-11-30'));
insert into test values('datediff(''2022-11-30'', time''10:10:10'')', datediff('2022-11-30', time'10:10:10'));
insert into test values('datediff(time''10:10:10'', time''10:10:10'')', datediff(time'10:10:10', time'10:10:10'));
insert into test values('datediff(101, ''2021-11-12'')', datediff(101, '2021-11-12'));
insert into test values('datediff(''2021-11-12'', 101)', datediff('2021-11-12', 101));
insert into test values('datediff(''2021-11-12'', 101)', datediff('2021-11-12', 101));
-- 特异
insert into test values('datediff(null, ''2021-11-12'')', datediff(null, '2021-11-12'));
insert into test values('datediff(''2021-11-12'', null)', datediff('2021-11-12', null));
insert into test values('datediff(null, null)', datediff(null, null));
-- 非严格模式，参数不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('datediff(''10000-01-01'', ''2021-11-12'')', datediff('10000-01-01', '2021-11-12'));
WARNING: Incorrect datetime value: "10000-01-01"
CONTEXT: referenced column: result
insert into test values('datediff(100000101, 20211112)', datediff(100000101, 20211112));
WARNING: Incorrect datetime value: "100000101"
CONTEXT: referenced column: result
insert into test values('datediff(99991231235959.9999995, 20211112)', datediff(99991231235959.9999995, 20211112));
WARNING: Truncated incorrect datetime value: "99991231235959.9999995"
CONTEXT: referenced column: result
insert into test values('datediff(9999999999999999999999, 20211112)', datediff(9999999999999999999999, 20211112));
WARNING: Incorrect datetime value: "9999999999999999999999"
CONTEXT: referenced column: result
-- 严格模式，参数不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('datediff(''10000-01-01'', ''2021-11-12'')', datediff('10000-01-01', '2021-11-12'));
ERROR: Incorrect datetime value: "10000-01-01"
CONTEXT: referenced column: result
insert into test values('datediff(100000101, 20211112)', datediff(100000101, 20211112));
ERROR: Incorrect datetime value: "100000101"
CONTEXT: referenced column: result
insert into test values('datediff(99991231235959.9999995, 20211112)', datediff(99991231235959.9999995, 20211112));
ERROR: Truncated incorrect datetime value: "99991231235959.9999995"
CONTEXT: referenced column: result
insert into test values('datediff(9999999999999999999999, 20211112)', datediff(9999999999999999999999, 20211112));
ERROR: Incorrect datetime value: "9999999999999999999999"
CONTEXT: referenced column: result
-- test from_days
-- 严格模式或者非严格模式都有值
-- 功能
insert into test values('from_days(2000000)', from_days(2000000));
insert into test values('from_days(''2000000'')', from_days('2000000'));
-- 边界
insert into test values('from_days(366)', from_days(366));
insert into test values('from_days(3652499)', from_days(3652499));
insert into test values('from_days(''366'')', from_days('366'));
insert into test values('from_days(''3652499'')', from_days('3652499'));
-- 特异
insert into test values('from_days(null)', from_days(null));
insert into test values('from_days(-1)', from_days(-1));
insert into test values('from_days(0)', from_days(0));
insert into test values('from_days(365)', from_days(365));
insert into test values('from_days(1111111111111111111)', from_days(1111111111111111111));
insert into test values('from_days(''1111111111111111111'')', from_days('1111111111111111111'));
-- test timestampdiff
set dolphin.b_compatibility_mode = true;
-- 严格模式或者非严格模式都有值
-- 功能
insert into test values('timestampdiff(microsecond, ''0000-1-1 00:00:00'', ''0000-1-2'')', timestampdiff(microsecond, '0000-1-1 00:00:00', '0000-1-2'));
insert into test values('timestampdiff(second, ''0000-1-1 00:00:00'', ''0000-1-2'')', timestampdiff(second, '0000-1-1 00:00:00', '0000-1-2'));
insert into test values('timestampdiff(minute, ''0000-1-1 00:00:00'', ''0000-1-2'')', timestampdiff(minute, '0000-1-1 00:00:00', '0000-1-2'));
insert into test values('timestampdiff(hour, ''0000-1-1'', ''0000-1-2'')', timestampdiff(hour, '0000-1-1', '0000-1-2'));
insert into test values('timestampdiff(year, ''9999-12-31'', ''0000-12-31'')', timestampdiff(year, '9999-12-31', '0000-12-31'));
insert into test values('timestampdiff(quarter, ''9999-12-31'', ''0000-12-31'')', timestampdiff(quarter, '9999-12-31', '0000-12-31'));
insert into test values('timestampdiff(month, ''9999-12-31'', ''0000-12-31'')', timestampdiff(month, '9999-12-31', '0000-12-31'));
insert into test values('timestampdiff(week, ''9999-12-31'', ''0000-12-31'')', timestampdiff(week, '9999-12-31', '0000-12-31'));
insert into test values('timestampdiff(day, ''9999-12-31'', ''0000-12-31'')', timestampdiff(day, '9999-12-31', '0000-12-31'));
insert into test values('timestampdiff(microsecond, time''-838:59:59'', time''838:59:59'')', timestampdiff(microsecond, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(second, time''-838:59:59'', time''838:59:59'')', timestampdiff(second, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(minute, time''-838:59:59'', time''838:59:59'')', timestampdiff(minute, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(hour, time''-838:59:59'', time''838:59:59'')', timestampdiff(hour, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(day, time''-838:59:59'', time''838:59:59'')', timestampdiff(day, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(month, time''-838:59:59'', time''838:59:59'')', timestampdiff(month, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(quarter, time''-838:59:59'', time''838:59:59'')', timestampdiff(quarter, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(year, time''-838:59:59'', time''838:59:59'')', timestampdiff(year, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(week, time''-838:59:59'', time''838:59:59'')', timestampdiff(week, time'-838:59:59', time'838:59:59'));
insert into test values('timestampdiff(microsecond, ''2022-9-12'', time''12:12:12'')', timestampdiff(microsecond, '2022-9-12', time'12:12:12'));
insert into test values('timestampdiff(microsecond, time''12:12:12'', ''2022-9-12'')', timestampdiff(microsecond, time'12:12:12', '2022-9-12'));
insert into test values('timestampdiff(microsecond, ''2022-9-12 12:12:12'', time''838:59:59'')', timestampdiff(microsecond, '2022-9-12 12:12:12', time'838:59:59'));
-- 边界
insert into test values('timestampdiff(microsecond, ''0000-1-1'', ''9999-12-31'')', timestampdiff(microsecond, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(second, ''0000-1-1'', ''9999-12-31'')', timestampdiff(second, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(minute, ''0000-1-1'', ''9999-12-31'')', timestampdiff(minute, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(hour, ''0000-1-1'', ''9999-12-31'')', timestampdiff(hour, '9999-12-31', '0000-12-31'));
insert into test values('timestampdiff(day, ''0000-1-1'', ''9999-12-31'')', timestampdiff(day, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(year, ''0000-1-1'', ''9999-12-31'')', timestampdiff(year, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(quarter, ''0000-1-1'', ''9999-12-31'')', timestampdiff(quarter, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(month, ''0000-1-1'', ''9999-12-31'')', timestampdiff(month, '0000-1-1', '9999-12-31'));
insert into test values('timestampdiff(week, ''0000-1-1'', ''9999-12-31'')', timestampdiff(week, '0000-1-1', '9999-12-31'));
-- 类型
insert into test values('timestampdiff(microsecond, cast(''2022-9-12 12:12:12.999999'' as datetime), time''12:12:12.999999'')', timestampdiff(microsecond, cast('2022-9-12 12:12:12.999999' as datetime), time'12:12:12.999999'));
insert into test values('timestampdiff(microsecond, time''12:12:12.999999'', cast(''2022-9-12 12:12:12.999999'' as datetime))', timestampdiff(microsecond, time'12:12:12.999999', cast('2022-9-12 12:12:12.999999' as datetime)));
insert into test values('timestampdiff(microsecond, date''2022-9-12'', time''12:12:12.999999'')',timestampdiff(microsecond, date'2022-9-12', time'12:12:12.999999'));
insert into test values('timestampdiff(microsecond, time''12:12:12.999999'', date''2022-9-12'')', timestampdiff(microsecond, time'12:12:12.999999', date'2022-9-12'));
insert into test values('timestampdiff(microsecond, ''20211112121212.999999'', ''20220912111111.999999'')', timestampdiff(microsecond, '20211112121212.999999', '20220912111111.999999'));
insert into test values('timestampdiff(microsecond, 101, ''20211112121212.999999'')', timestampdiff(microsecond, 101, '20211112121212.999999'));
insert into test values('timestampdiff(microsecond, ''20211112121212.999999'', 101)', timestampdiff(microsecond, '20211112121212.999999', 101));
insert into test values('timestampdiff(microsecond, 99991231235959.999999, 101)', timestampdiff(microsecond, 99991231235959.999999, 101));
insert into test values('timestampdiff(microsecond, 101, 99991231235959.999999)', timestampdiff(microsecond, 101, 99991231235959.999999));
-- 特异
insert into test values('timestampdiff(week, null, ''9999-12-31'')', timestampdiff(week, null, '9999-12-31'));
insert into test values('timestampdiff(week, ''9999-12-31'', null)', timestampdiff(week, '9999-12-31', null));
insert into test values('timestampdiff(week, null, null)', timestampdiff(week, null, null));
--严格模式下，针对unit的非法报error
insert into test values('timestampdiff(abc, ''2020-11-11'', ''2020-11-12'')', timestampdiff(abc, '2020-11-11', '2020-11-12'));
ERROR: timestamp with time zone units "abc" not recognized
CONTEXT: referenced column: result
insert into test values('timestampdiff(decades, ''2020-11-11'', ''2020-11-12'')', timestampdiff(decades, '2020-11-11', '2020-11-12'));
ERROR: timestamp units "decades" not supported
CONTEXT: referenced column: result
--严格模式下，针对value的非法报warning
insert into test values('timestampdiff(day, ''2020-11-11'', ''abc'')', timestampdiff(day, '2020-11-11', 'abc'));
ERROR: Incorrect datetime value: "abc"
CONTEXT: referenced column: result
-- 非严格模式，参数不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('timestampdiff(microsecond, ''abc'', ''2021-11-12'')', timestampdiff(microsecond, 'abc', '2021-11-12'));
WARNING: Incorrect datetime value: "abc"
CONTEXT: referenced column: result
insert into test values('timestampdiff(microsecond, ''2021-11-12'', ''10000-1-1'')', timestampdiff(microsecond, '2021-11-12', '10000-1-1'));
WARNING: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('timestampdiff(microsecond, ''10000-1-1'', ''2021-11-12'')', timestampdiff(microsecond, '10000-1-1', '2021-11-12'));
WARNING: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
--非严格模式下，unit不合法，仍然报error
insert into test values('timestampdiff(abc, ''2020-11-11'', ''2020-11-12'')', timestampdiff(abc, '2020-11-11', '2020-11-12'));
ERROR: timestamp with time zone units "abc" not recognized
CONTEXT: referenced column: result
insert into test values('timestampdiff(decades, ''2020-11-11'', ''2020-11-12'')', timestampdiff(decades, '2020-11-11', '2020-11-12'));
ERROR: timestamp units "decades" not supported
CONTEXT: referenced column: result
-- 严格模式，参数不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('timestampdiff(microsecond, ''abc'', ''2021-11-12'')', timestampdiff(microsecond, 'abc', '2021-11-12'));
ERROR: Incorrect datetime value: "abc"
CONTEXT: referenced column: result
insert into test values('timestampdiff(microsecond, ''2021-11-12'', ''10000-1-1'')', timestampdiff(microsecond, '2021-11-12', '10000-1-1'));
ERROR: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('timestampdiff(microsecond, ''10000-1-1'', ''2021-11-12'')', timestampdiff(microsecond, '10000-1-1', '2021-11-12'));
ERROR: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('timestampdiff(abc, ''2020-11-11'', ''2020-11-12'')', timestampdiff(abc, '2020-11-11', '2020-11-12'));
ERROR: timestamp with time zone units "abc" not recognized
CONTEXT: referenced column: result
insert into test values('timestampdiff(decades, ''2020-11-11'', ''2020-11-12'')', timestampdiff(decades, '2020-11-11', '2020-11-12'));
ERROR: timestamp units "decades" not supported
CONTEXT: referenced column: result
insert into test values('timestampdiff(day, ''2020-11-11'', ''abc'')', timestampdiff(day, '2020-11-11', 'abc'));
ERROR: Incorrect datetime value: "abc"
CONTEXT: referenced column: result
-- SQL_TSI_ 兼容
select timestampdiff(SQL_TSI_SECOND, '2001-02-01', '2001-05-01') = timestampdiff(SECOND, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_MINUTE, '2001-02-01', '2001-05-01') = timestampdiff(MINUTE, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') = timestampdiff(HOUR, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') = timestampdiff(DAY, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') = timestampdiff(WEEK, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_MONTH, '2001-02-01', '2001-05-01') = timestampdiff(MONTH, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_QUARTER, '2001-02-01', '2001-05-01') = timestampdiff(QUARTER, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select timestampdiff(SQL_TSI_YEAR, '2001-02-01', '2001-05-01') = timestampdiff(YEAR, '2001-02-01', '2001-05-01');
?column?
----------
t
(1 row)
select TIMESTAMPDIFF(HOUR,'2024-12-31 00:00:00','2024-12-31 20:12:00') ;
timestampdiff
---------------
20
(1 row)
select TIMESTAMPDIFF(DAY,'2024-12-30 00:00:00','2024-12-30 20:12:00') ;
timestampdiff
---------------
0
(1 row)
select TIMESTAMPDIFF(DAY,'2024-12-31 00:00:00','2024-12-31 20:12:00') ;
timestampdiff
---------------
0
(1 row)
select TIMESTAMPDIFF(DAY,'2023-12-31 00:00:00','2023-12-31 20:12:00') ;
timestampdiff
---------------
0
(1 row)
select TIMESTAMPDIFF(HOUR,'2025-1-1 01:00:00','2024-12-31 20:12:00') ;
timestampdiff
---------------
-4
(1 row)
select TIMESTAMPDIFF(HOUR,'2024-12-31 20:12:00', '2025-1-1 01:00:00') ;
timestampdiff
---------------
4
(1 row)
set dolphin.b_compatibility_mode = false;
-- test convert_tz
-- 严格模式或者非严格模式都有值
-- 双字母,第一次转换超[1970-1-1 00:00:01.000000, 2038-01-19 03:14:07.999999]，结果不转换
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''MET'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'MET', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''CCT'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'CCT', 'GMT'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''EDT'', ''GMT'')', convert_tz('2038-01-19 03:14:07.999999', 'EDT', 'GMT'));
-- 双字母正常转换
insert into test values('convert_tz(''1234-1-1 00:00:00'', ''MET'', ''GMT'')', convert_tz('1234-1-1 00:00:00', 'MET', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''GMT'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'GMT', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''EDT'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'EDT', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''CDT'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'CDT', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''EST'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'EST', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''MDT'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'MDT', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''MST'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', 'MST', 'GMT'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''JST'', ''GMT'')',convert_tz('2038-01-19 03:14:07.999999', 'JST', 'GMT'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''KST'', ''GMT'')',convert_tz('2038-01-19 03:14:07.999999', 'KST', 'GMT'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''CCT'', ''GMT'')',convert_tz('2038-01-19 03:14:07.999999', 'CCT', 'GMT'));
-- 双数字,第一次转换超[1970-1-1 00:00:01.000000, 2038-01-19 03:14:07.999999]，结果不转换
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''+1:00'', ''+0:00'')', convert_tz('1970-1-1 00:00:01.000000', '+1:00', '+0:00'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''-1:00'', ''+0:00'')', convert_tz('1970-1-1 00:00:01.000000', '+1:00', '+0:00'));
-- 双数字正常转换
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''-1:00'', ''+0:00'')', convert_tz('1970-1-1 00:00:01.000000', '-1:00', '+0:00'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''-2:00'', ''+0:00'')', convert_tz('1970-1-1 00:00:01.000000', '-2:00', '+0:00'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''-10:00'', ''+0:00'')', convert_tz('1970-1-1 00:00:01.000000', '-10:00', '+0:00'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''+1:00'', ''+0:00'')', convert_tz('2038-01-19 03:14:07.999999', '+1:00', '+0:00'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''+2:00'', ''+0:00'')', convert_tz('2038-01-19 03:14:07.999999', '+2:00', '+0:00'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''+10:00'', ''+0:00'')', convert_tz('2038-01-19 03:14:07.999999', '+10:00', '+0:00'));
-- 字母数字混合
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''-1:00'', ''GMT'')', convert_tz('1970-1-1 00:00:01.000000', '-1:00', 'GMT'));
insert into test values('convert_tz(''1970-1-1 00:00:01.000000'', ''GMT'', ''+3:00'')', convert_tz('1970-1-1 00:00:01.000000', 'GMT', '+3:00'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''+2:00'', ''GMT'')', convert_tz('2038-01-19 03:14:07.999999', '+2:00', 'GMT'));
insert into test values('convert_tz(''2038-01-19 03:14:07.999999'', ''GMT'', ''+7:00'')', convert_tz('2038-01-19 03:14:07.999999', 'GMT', '+7:00'));
insert into test values('convert_tz(time''10:10:10'', ''GMT'', ''+7:00'')', convert_tz(time'10:10:10', 'GMT', '+7:00'));
insert into test values('convert_tz(101, ''GMT'', ''+7:00'')', convert_tz(101, 'GMT', '+7:00'));
-- 特异
insert into test values('convert_tz(null, ''+1:00'', ''+0:00'')', convert_tz(null, '+1:00', '+0:00'));
insert into test values('convert_tz(''2021-11-12 12:12:12'', null, ''+0:00'')', convert_tz('2021-11-12 12:12:12', null, '+0:00'));
insert into test values('convert_tz(''2021-11-12 12:12:12'', ''+1:00'', null)', convert_tz('2021-11-12 12:12:12', '+1:00', null));
insert into test values('convert_tz(''2021-11-12 12:12:12'', ''abc'', ''+0:00'')', convert_tz('2021-11-12 12:12:12', 'abc', '+0:00'));
insert into test values('convert_tz(''2021-11-12 12:12:12'', ''+1:00'', ''abc'')', convert_tz('2021-11-12 12:12:12', '+1:00', 'abc'));
insert into test values('convert_tz(''2021-11-12 12:12:12'', ''+13:01'', ''+0:00'')', convert_tz('2021-11-12 12:12:12', '+13:01', '+0:00'));
insert into test values('convert_tz(''2021-11-12 12:12:12'', ''+0:00'', ''-13:00'')', convert_tz('2021-11-12 12:12:12', '+0:00', '-13:00'));
-- 非严格模式，参数不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('convert_tz(''10000-1-1 10:10:10'', ''+1:00'', ''+0:00'')', convert_tz('10000-1-1 10:10:10', '+1:00', '+0:00'));
WARNING: Incorrect datetime value: "10000-1-1 10:10:10"
CONTEXT: referenced column: result
-- 严格模式，参数不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('convert_tz(''10000-1-1 10:10:10'', ''+1:00'', ''+0:00'')', convert_tz('10000-1-1 10:10:10', '+1:00', '+0:00'));
ERROR: Incorrect datetime value: "10000-1-1 10:10:10"
CONTEXT: referenced column: result
-- test adddate(date/datetime/time, INTERVAL/days)
-- 严格模式或者非严格模式都有值
-- 功能-unit
insert into test values('adddate(''0001-01-01'',INTERVAL 1 year)', adddate('0001-01-01',INTERVAL 1 year));
insert into test values('adddate(''0001-01-01'',INTERVAL 1 day)', adddate('0001-01-01',INTERVAL 1 day));
insert into test values('adddate(''0001-01-01'',INTERVAL 1 month)', adddate('0001-01-01',INTERVAL 1 month));
insert into test values('adddate(''0001-01-01'',INTERVAL 1 hour)', adddate('0001-01-01',INTERVAL 1 hour));
insert into test values('adddate(''0001-01-01'',INTERVAL 1 minute)', adddate('0001-01-01',INTERVAL 1 minute));
insert into test values('adddate(''0001-01-01'',INTERVAL 1 second)', adddate('0001-01-01',INTERVAL 1 second));
insert into test values('adddate(''0001-01-01 12:12:12'',INTERVAL 1 hour)', adddate('0001-01-01 12:12:12',INTERVAL 1 hour));
insert into test values('adddate(''0001-01-01 12:12:12'',INTERVAL 1 minute)', adddate('0001-01-01 12:12:12',INTERVAL 1 minute));
insert into test values('adddate(''0001-01-01 12:12:12'',INTERVAL 1 second)', adddate('0001-01-01 12:12:12',INTERVAL 1 second));
insert into test values('adddate(''9999-12-31'',INTERVAL -1 year)', adddate('9999-12-31',INTERVAL -1 year));
insert into test values('adddate(''9999-12-31'',INTERVAL -1 day)', adddate('9999-12-31',INTERVAL -1 day));
insert into test values('adddate(''9999-12-31'',INTERVAL -1 month)', adddate('9999-12-31',INTERVAL -1 month));
insert into test values('adddate(''9999-12-31'',INTERVAL -1 hour)', adddate('9999-12-31',INTERVAL -1 hour));
insert into test values('adddate(''9999-12-31'',INTERVAL -1 minute)', adddate('9999-12-31',INTERVAL -1 minute));
insert into test values('adddate(''9999-12-31'',INTERVAL -1 second)', adddate('9999-12-31',INTERVAL -1 second));
insert into test values('adddate(''9999-12-31 12:12:12'',INTERVAL -1 hour)', adddate('9999-12-31 12:12:12',INTERVAL -1 hour));
insert into test values('adddate(''9999-12-31 12:12:12'',INTERVAL -1 minute)', adddate('9999-12-31 12:12:12',INTERVAL -1 minute));
insert into test values('adddate(''9999-12-31 12:12:12'',INTERVAL -1 second)', adddate('9999-12-31 12:12:12',INTERVAL -1 second));
insert into test values('adddate(''2021-11-12'', -1)', adddate('2021-11-12', -1));
insert into test values('adddate(''2021-11-12'', 1)', adddate('2021-11-12', 1));
insert into test values('adddate(''11:11:11'', 2)', adddate('11:11:11', 2));
insert into test values('adddate(''11:11:11'', INTERVAL 2 day)', adddate('11:11:11', INTERVAL 2 day));
insert into test values('adddate(''11:11:11'', INTERVAL 2 second)', adddate('11:11:11', INTERVAL 2 second));
-- 功能-一些进位情况
insert into test values('adddate(''0001-12-31'',INTERVAL 1 day)',adddate('0001-12-31',INTERVAL 1 day));
insert into test values('adddate(''2021-12-31'',INTERVAL 1 day)',adddate('2021-12-31',INTERVAL 1 day));
insert into test values('adddate(''9998-12-31'',INTERVAL 1 day)',adddate('9998-12-31',INTERVAL 1 day));
insert into test values('adddate(''0001-12-31'',INTERVAL 1 month)',adddate('0001-12-31',INTERVAL 1 month));
insert into test values('adddate(''1999-12-31'',INTERVAL 1 day)',adddate('1999-12-31',INTERVAL 1 day));
insert into test values('adddate(''1999-12-31'',INTERVAL 1 year)',adddate('1999-12-31',INTERVAL 1 year));
insert into test values('adddate(''1999-12-31 23:59:59'',INTERVAL 1 second)',adddate('1999-12-31 23:59:59',INTERVAL 1 second));
insert into test values('adddate(''1999-12-31 23:59:00'',INTERVAL 1 minute)',adddate('1999-12-31 23:59:00',INTERVAL 1 minute));
insert into test values('adddate(''1999-12-31 23:00:00'',INTERVAL 1 hour)',adddate('1999-12-31 23:00:00',INTERVAL 1 hour));
-- 功能-第一参数为显示time类型
insert into test values('adddate(time''12:12:12'', INTERVAL 1 day)', adddate(time'12:12:12', INTERVAL 1 day));
insert into test values('adddate(time''12:12:12'', INTERVAL 1 hour)', adddate(time'12:12:12', INTERVAL 1 hour));
insert into test values('adddate(time''12:12:12'', INTERVAL 1 minute)', adddate(time'12:12:12', INTERVAL 1 minute));
insert into test values('adddate(time''12:12:12'', INTERVAL 1 second)', adddate(time'12:12:12', INTERVAL 1 second));
insert into test values('adddate(time''12:12:12'', INTERVAL -1 day)', adddate(time'12:12:12', INTERVAL -1 day));
insert into test values('adddate(time''12:12:12'', INTERVAL -1 hour)', adddate(time'12:12:12', INTERVAL -1 hour));
insert into test values('adddate(time''12:12:12'', INTERVAL -1 minute)', adddate(time'12:12:12', INTERVAL -1 minute));
insert into test values('adddate(time''12:12:12'', INTERVAL -1 second)', adddate(time'12:12:12', INTERVAL -1 second));
insert into test values('adddate(time''12:12:12'', 1)', adddate(time'12:12:12', 1));
insert into test values('adddate(time''12:12:12'', -1)', adddate(time'12:12:12', -1));
-- 类型
insert into test values('adddate(date''0001-01-01'',INTERVAL 1 year)', adddate(date'0001-01-01',INTERVAL 1 year));
insert into test values('adddate(cast(''9999-12-31 12:12:12'' as datetime),INTERVAL -1 second)', adddate(cast('9999-12-31 12:12:12' as datetime), INTERVAL -1 second));
insert into test values('adddate(time''838:59:59'', INTERVAL -1 second)', adddate(time'838:59:59',INTERVAL -1 second));
insert into test values('adddate(101, INTERVAL 1 year)', adddate(101, INTERVAL 1 year));
insert into test values('adddate(101, INTERVAL 100 second)', adddate(101, INTERVAL 100 second));
insert into test values('adddate(101, 1)', adddate(101, 1));
-- 特异
insert into test values('adddate(null, INTERVAL 1 day)', adddate(null, INTERVAL 1 day));
insert into test values('adddate(''2021-11-12'', null)', adddate('2021-11-12', null));
insert into test values('adddate(null, null)', adddate(null, null));
-- 非严格模式，参数或者结果不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('adddate(0, INTERVAL 100 second)', adddate(0, INTERVAL 100 second));
WARNING: Incorrect datetime value: "0"
CONTEXT: referenced column: result
insert into test values('adddate(''10000-1-1'', INTERVAL -1 day)', adddate('10000-1-1', INTERVAL -1 day));
WARNING: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31'', INTERVAL 1 day)', adddate('9999-12-31', INTERVAL 1 day));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31'', INTERVAL 1 month)', adddate('9999-12-31', INTERVAL 1 month));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31'', INTERVAL 1 year)', adddate('9999-12-31', INTERVAL 1 year));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31 23:59:59'', INTERVAL 1 second)', adddate('9999-12-31 23:59:59', INTERVAL 1 second));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31 23:59:00'', INTERVAL 1 minute)', adddate('9999-12-31 23:59:00', INTERVAL 1 minute));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31 23:00:00'', INTERVAL 1 hour)', adddate('9999-12-31 23:00:00', INTERVAL 1 hour));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(time''838:59:59'', INTERVAL 1 second)', adddate(time'838:59:59', INTERVAL 1 second));
WARNING: time field value out of range
CONTEXT: referenced column: result
insert into test values('adddate(time''-838:59:59'', INTERVAL -1 second)', adddate(time'-838:59:59', INTERVAL -1 second));
WARNING: time field value out of range
CONTEXT: referenced column: result
-- 严格模式，参数不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('adddate(0, INTERVAL 100 second)', adddate(0, INTERVAL 100 second));
ERROR: Incorrect datetime value: "0"
CONTEXT: referenced column: result
insert into test values('adddate(''10000-1-1'', INTERVAL -1 day)', adddate('10000-1-1', INTERVAL -1 day));
ERROR: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31'', INTERVAL 1 day)', adddate('9999-12-31', INTERVAL 1 day));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31'', INTERVAL 1 month)', adddate('9999-12-31', INTERVAL 1 month));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31'', INTERVAL 1 year)', adddate('9999-12-31', INTERVAL 1 year));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31 23:59:59'', INTERVAL 1 second)', adddate('9999-12-31 23:59:59', INTERVAL 1 second));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31 23:59:00'', INTERVAL 1 minute)', adddate('9999-12-31 23:59:00', INTERVAL 1 minute));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(''9999-12-31 23:00:00'', INTERVAL 1 hour)', adddate('9999-12-31 23:00:00', INTERVAL 1 hour));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('adddate(time''838:59:59'', INTERVAL 1 second)', adddate(time'838:59:59', INTERVAL 1 second));
ERROR: time field value out of range
CONTEXT: referenced column: result
insert into test values('adddate(time''-838:59:59'', INTERVAL -1 second)', adddate(time'-838:59:59', INTERVAL -1 second));
ERROR: time field value out of range
CONTEXT: referenced column: result
-- 结果小于'0001-1-1 00:00:00'时，结果中日期部分置为'0000-00-00'，时间部分为相应值。
insert into test values('adddate(''0001-1-1'', INTERVAL -1 day)', adddate('0001-1-1', INTERVAL -1 day));
insert into test values('adddate(''0001-1-1'', -1)', adddate('0001-1-1', -1));
insert into test values('adddate(''0001-1-1 00:00:00'', INTERVAL -1 second)', adddate('0001-1-1 00:00:00', INTERVAL -1 second));
-- 结果超越边界，严格模式报错，非严格模式报warning，返回NULL。
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('adddate(''00010101'', -367)', adddate('00010101', -367));
ERROR: date/time field value out of range
CONTEXT: referenced column: result
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('adddate(''00010101'', -367)', adddate('00010101', -367));
WARNING: date/time field value out of range
CONTEXT: referenced column: result
-- test date_add(date/datetime/time, INTERVAL)
-- 严格模式或者非严格模式都有值
-- 功能-unit
insert into test values('date_add(''0001-01-01'',INTERVAL 1 year)', date_add('0001-01-01',INTERVAL 1 year));
insert into test values('date_add(''0001-01-01'',INTERVAL 1 day)', date_add('0001-01-01',INTERVAL 1 day));
insert into test values('date_add(''0001-01-01'',INTERVAL 1 month)', date_add('0001-01-01',INTERVAL 1 month));
insert into test values('date_add(''0001-01-01'',INTERVAL 1 hour)', date_add('0001-01-01',INTERVAL 1 hour));
insert into test values('date_add(''0001-01-01'',INTERVAL 1 minute)', date_add('0001-01-01',INTERVAL 1 minute));
insert into test values('date_add(''0001-01-01'',INTERVAL 1 second)', date_add('0001-01-01',INTERVAL 1 second));
insert into test values('date_add(''0001-01-01 12:12:12'',INTERVAL 1 hour)', date_add('0001-01-01 12:12:12',INTERVAL 1 hour));
insert into test values('date_add(''0001-01-01 12:12:12'',INTERVAL 1 minute)', date_add('0001-01-01 12:12:12',INTERVAL 1 minute));
insert into test values('date_add(''0001-01-01 12:12:12'',INTERVAL 1 second)', date_add('0001-01-01 12:12:12',INTERVAL 1 second));
insert into test values('date_add(''9999-12-31'',INTERVAL -1 year)', date_add('9999-12-31',INTERVAL -1 year));
insert into test values('date_add(''9999-12-31'',INTERVAL -1 day)', date_add('9999-12-31',INTERVAL -1 day));
insert into test values('date_add(''9999-12-31'',INTERVAL -1 month)', date_add('9999-12-31',INTERVAL -1 month));
insert into test values('date_add(''9999-12-31'',INTERVAL -1 hour)', date_add('9999-12-31',INTERVAL -1 hour));
insert into test values('date_add(''9999-12-31'',INTERVAL -1 minute)', date_add('9999-12-31',INTERVAL -1 minute));
insert into test values('date_add(''9999-12-31'',INTERVAL -1 second)', date_add('9999-12-31',INTERVAL -1 second));
insert into test values('date_add(''9999-12-31 12:12:12'',INTERVAL -1 hour)', date_add('9999-12-31 12:12:12',INTERVAL -1 hour));
insert into test values('date_add(''9999-12-31 12:12:12'',INTERVAL -1 minute)', date_add('9999-12-31 12:12:12',INTERVAL -1 minute));
insert into test values('date_add(''9999-12-31 12:12:12'',INTERVAL -1 second)', date_add('9999-12-31 12:12:12',INTERVAL -1 second));
-- 功能-一些进位情况
insert into test values('date_add(''0001-12-31'',INTERVAL 1 day)',date_add('0001-12-31',INTERVAL 1 day));
insert into test values('date_add(''2021-12-31'',INTERVAL 1 day)',date_add('2021-12-31',INTERVAL 1 day));
insert into test values('date_add(''9998-12-31'',INTERVAL 1 day)',date_add('9998-12-31',INTERVAL 1 day));
insert into test values('date_add(''0001-12-31'',INTERVAL 1 month)',date_add('0001-12-31',INTERVAL 1 month));
insert into test values('date_add(''1999-12-31'',INTERVAL 1 day)',date_add('1999-12-31',INTERVAL 1 day));
insert into test values('date_add(''1999-12-31'',INTERVAL 1 year)',date_add('1999-12-31',INTERVAL 1 year));
insert into test values('date_add(''1999-12-31 23:59:59'',INTERVAL 1 second)',date_add('1999-12-31 23:59:59',INTERVAL 1 second));
insert into test values('date_add(''1999-12-31 23:59:00'',INTERVAL 1 minute)',date_add('1999-12-31 23:59:00',INTERVAL 1 minute));
insert into test values('date_add(''1999-12-31 23:00:00'',INTERVAL 1 hour)',date_add('1999-12-31 23:00:00',INTERVAL 1 hour));
-- 功能-第一参数为显示time类型
insert into test values('date_add(time''12:12:12'', INTERVAL 1 day)', date_add(time'12:12:12', INTERVAL 1 day));
insert into test values('date_add(time''12:12:12'', INTERVAL 1 hour)', date_add(time'12:12:12', INTERVAL 1 hour));
insert into test values('date_add(time''12:12:12'', INTERVAL 1 minute)', date_add(time'12:12:12', INTERVAL 1 minute));
insert into test values('date_add(time''12:12:12'', INTERVAL 1 second)', date_add(time'12:12:12', INTERVAL 1 second));
insert into test values('date_add(time''12:12:12'', INTERVAL -1 day)', date_add(time'12:12:12', INTERVAL -1 day));
insert into test values('date_add(time''12:12:12'', INTERVAL -1 hour)', date_add(time'12:12:12', INTERVAL -1 hour));
insert into test values('date_add(time''12:12:12'', INTERVAL -1 minute)', date_add(time'12:12:12', INTERVAL -1 minute));
insert into test values('date_add(time''12:12:12'', INTERVAL -1 second)', date_add(time'12:12:12', INTERVAL -1 second));
-- 类型
insert into test values('date_add(date''0001-01-01'',INTERVAL 1 year)', date_add(date'0001-01-01',INTERVAL 1 year));
insert into test values('date_add(cast(''9999-12-31 12:12:12'' as datetime),INTERVAL -1 second)', date_add(cast('9999-12-31 12:12:12' as datetime), INTERVAL -1 second));
insert into test values('date_add(time''838:59:59'', INTERVAL -1 second)', date_add(time'838:59:59',INTERVAL -1 second));
insert into test values('date_add(101, INTERVAL 1 year)', date_add(101, INTERVAL 1 year));
insert into test values('date_add(101, INTERVAL 100 second)', date_add(101, INTERVAL 100 second));
-- 特异
insert into test values('date_add(null, INTERVAL 1 day)', date_add(null, INTERVAL 1 day));
-- 非严格模式，参数或者结果不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_add(0, INTERVAL 100 second)', date_add(0, INTERVAL 100 second));
WARNING: Incorrect datetime value: "0"
CONTEXT: referenced column: result
insert into test values('date_add(''10000-1-1'', INTERVAL -1 day)', date_add('10000-1-1', INTERVAL -1 day));
WARNING: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31'', INTERVAL 1 day)', date_add('9999-12-31', INTERVAL 1 day));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31'', INTERVAL 1 month)', date_add('9999-12-31', INTERVAL 1 month));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31'', INTERVAL 1 year)', date_add('9999-12-31', INTERVAL 1 year));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31 23:59:59'', INTERVAL 1 second)', date_add('9999-12-31 23:59:59', INTERVAL 1 second));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31 23:59:00'', INTERVAL 1 minute)', date_add('9999-12-31 23:59:00', INTERVAL 1 minute));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31 23:00:00'', INTERVAL 1 hour)', date_add('9999-12-31 23:00:00', INTERVAL 1 hour));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(time''838:59:59'', INTERVAL 1 second)', date_add(time'838:59:59', INTERVAL 1 second));
WARNING: time field value out of range
CONTEXT: referenced column: result
insert into test values('date_add(time''-838:59:59'', INTERVAL -1 second)', date_add(time'-838:59:59', INTERVAL -1 second));
WARNING: time field value out of range
CONTEXT: referenced column: result
-- 严格模式，参数不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_add(0, INTERVAL 100 second)', date_add(0, INTERVAL 100 second));
ERROR: Incorrect datetime value: "0"
CONTEXT: referenced column: result
insert into test values('date_add(''10000-1-1'', INTERVAL -1 day)', date_add('10000-1-1', INTERVAL -1 day));
ERROR: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31'', INTERVAL 1 day)', date_add('9999-12-31', INTERVAL 1 day));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31'', INTERVAL 1 month)', date_add('9999-12-31', INTERVAL 1 month));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31'', INTERVAL 1 year)', date_add('9999-12-31', INTERVAL 1 year));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31 23:59:59'', INTERVAL 1 second)', date_add('9999-12-31 23:59:59', INTERVAL 1 second));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31 23:59:00'', INTERVAL 1 minute)', date_add('9999-12-31 23:59:00', INTERVAL 1 minute));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(''9999-12-31 23:00:00'', INTERVAL 1 hour)', date_add('9999-12-31 23:00:00', INTERVAL 1 hour));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_add(time''838:59:59'', INTERVAL 1 second)', date_add(time'838:59:59', INTERVAL 1 second));
ERROR: time field value out of range
CONTEXT: referenced column: result
insert into test values('date_add(time''-838:59:59'', INTERVAL -1 second)', date_add(time'-838:59:59', INTERVAL -1 second));
ERROR: time field value out of range
CONTEXT: referenced column: result
-- 结果小于'0001-1-1 00:00:00'时，结果中日期部分置为'0000-00-00'，时间部分为相应值。
insert into test values('date_add(''0001-1-1'', INTERVAL -1 day)', date_add('0001-1-1', INTERVAL -1 day));
insert into test values('date_add(''0001-1-1 00:00:00'', INTERVAL -1 second)', date_add('0001-1-1 00:00:00', INTERVAL -1 second));
-- 结果超越边界，严格模式报错，非严格模式报warning，返回NULL。
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_add(''00010101'', INTERVAL -367 day)', date_add('00010101', INTERVAL -367 day));
ERROR: date/time field overflow
CONTEXT: referenced column: result
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_add(''00010101'', INTERVAL -367 day)', date_add('00010101', INTERVAL -367 day));
WARNING: date/time field overflow
CONTEXT: referenced column: result
-- test date_sub(date/datetime/time, INTERVAL)
-- 严格模式或者非严格模式都有值
-- 功能-unit
insert into test values('date_sub(''0001-01-01'',INTERVAL -1 year)', date_sub('0001-01-01',INTERVAL -1 year));
insert into test values('date_sub(''0001-01-01'',INTERVAL -1 day)', date_sub('0001-01-01',INTERVAL -1 day));
insert into test values('date_sub(''0001-01-01'',INTERVAL -1 month)', date_sub('0001-01-01',INTERVAL -1 month));
insert into test values('date_sub(''0001-01-01'',INTERVAL -1 hour)', date_sub('0001-01-01',INTERVAL -1 hour));
insert into test values('date_sub(''0001-01-01'',INTERVAL -1 minute)', date_sub('0001-01-01',INTERVAL -1 minute));
insert into test values('date_sub(''0001-01-01'',INTERVAL -1 second)', date_sub('0001-01-01',INTERVAL -1 second));
insert into test values('date_sub(''0001-01-01 12:12:12'',INTERVAL -1 hour)', date_sub('0001-01-01 12:12:12',INTERVAL -1 hour));
insert into test values('date_sub(''0001-01-01 12:12:12'',INTERVAL -1 minute)', date_sub('0001-01-01 12:12:12',INTERVAL -1 minute));
insert into test values('date_sub(''0001-01-01 12:12:12'',INTERVAL -1 second)', date_sub('0001-01-01 12:12:12',INTERVAL -1 second));
insert into test values('date_sub(''9999-12-31'',INTERVAL 1 year)', date_sub('9999-12-31',INTERVAL 1 year));
insert into test values('date_sub(''9999-12-31'',INTERVAL 1 day)', date_sub('9999-12-31',INTERVAL 1 day));
insert into test values('date_sub(''9999-12-31'',INTERVAL 1 month)', date_sub('9999-12-31',INTERVAL 1 month));
insert into test values('date_sub(''9999-12-31'',INTERVAL 1 hour)', date_sub('9999-12-31',INTERVAL 1 hour));
insert into test values('date_sub(''9999-12-31'',INTERVAL 1 minute)', date_sub('9999-12-31',INTERVAL 1 minute));
insert into test values('date_sub(''9999-12-31'',INTERVAL 1 second)', date_sub('9999-12-31',INTERVAL 1 second));
insert into test values('date_sub(''9999-12-31 12:12:12'',INTERVAL 1 hour)', date_sub('9999-12-31 12:12:12',INTERVAL 1 hour));
insert into test values('date_sub(''9999-12-31 12:12:12'',INTERVAL 1 minute)', date_sub('9999-12-31 12:12:12',INTERVAL 1 minute));
insert into test values('date_sub(''9999-12-31 12:12:12'',INTERVAL 1 second)', date_sub('9999-12-31 12:12:12',INTERVAL 1 second));
-- 功能-一些进位情况
insert into test values('date_sub(''0001-12-31'',INTERVAL -1 day)',date_sub('0001-12-31',INTERVAL -1 day));
insert into test values('date_sub(''2021-12-31'',INTERVAL -1 day)',date_sub('2021-12-31',INTERVAL -1 day));
insert into test values('date_sub(''9998-12-31'',INTERVAL -1 day)',date_sub('9998-12-31',INTERVAL -1 day));
insert into test values('date_sub(''0001-12-31'',INTERVAL -1 month)',date_sub('0001-12-31',INTERVAL -1 month));
insert into test values('date_sub(''1999-12-31'',INTERVAL -1 day)',date_sub('1999-12-31',INTERVAL -1 day));
insert into test values('date_sub(''1999-12-31'',INTERVAL -1 year)',date_sub('1999-12-31',INTERVAL -1 year));
insert into test values('date_sub(''1999-12-31 23:59:59'',INTERVAL -1 second)',date_sub('1999-12-31 23:59:59',INTERVAL -1 second));
insert into test values('date_sub(''1999-12-31 23:59:00'',INTERVAL -1 minute)',date_sub('1999-12-31 23:59:00',INTERVAL -1 minute));
insert into test values('date_sub(''1999-12-31 23:00:00'',INTERVAL -1 hour)',date_sub('1999-12-31 23:00:00',INTERVAL -1 hour));
-- 功能-第一参数为显示time类型
insert into test values('date_sub(time''12:12:12'', INTERVAL 1 day)', date_sub(time'12:12:12', INTERVAL 1 day));
insert into test values('date_sub(time''12:12:12'', INTERVAL 1 hour)', date_sub(time'12:12:12', INTERVAL 1 hour));
insert into test values('date_sub(time''12:12:12'', INTERVAL 1 minute)', date_sub(time'12:12:12', INTERVAL 1 minute));
insert into test values('date_sub(time''12:12:12'', INTERVAL 1 second)', date_sub(time'12:12:12', INTERVAL 1 second));
insert into test values('date_sub(time''12:12:12'', INTERVAL -1 day)', date_sub(time'12:12:12', INTERVAL -1 day));
insert into test values('date_sub(time''12:12:12'', INTERVAL -1 hour)', date_sub(time'12:12:12', INTERVAL -1 hour));
insert into test values('date_sub(time''12:12:12'', INTERVAL -1 minute)', date_sub(time'12:12:12', INTERVAL -1 minute));
insert into test values('date_sub(time''12:12:12'', INTERVAL -1 second)', date_sub(time'12:12:12', INTERVAL -1 second));
-- 类型
insert into test values('date_sub(date''0001-01-01'',INTERVAL -1 year)', date_sub(date'0001-01-01',INTERVAL -1 year));
insert into test values('date_sub(cast(''9999-12-31 12:12:12'' as datetime),INTERVAL -1 second)', date_sub(cast('9999-12-31 12:12:12' as datetime) ,INTERVAL -1 second));
insert into test values('date_sub(time''838:59:59'', INTERVAL 1 second)', date_sub(time'838:59:59',INTERVAL 1 second));
insert into test values('date_sub(101, INTERVAL 1 year)', date_sub(101, INTERVAL 1 year));
insert into test values('date_sub(101, INTERVAL 100 second)', date_sub(101, INTERVAL 100 second));
-- 特异
insert into test values('date_sub(null, INTERVAL 1 day)', date_sub(null, INTERVAL 1 day));
-- 非严格模式，参数或者结果不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_sub(0, INTERVAL 100 second)', date_sub(0, INTERVAL 100 second));
WARNING: Incorrect datetime value: "0"
CONTEXT: referenced column: result
insert into test values('date_sub(''10000-1-1'', INTERVAL 1 day)', date_sub('10000-1-1', INTERVAL 1 day));
WARNING: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31'', INTERVAL -1 day)', date_sub('9999-12-31', INTERVAL -1 day));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31'', INTERVAL -1 month)', date_sub('9999-12-31', INTERVAL -1 month));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31'', INTERVAL -1 year)', date_sub('9999-12-31', INTERVAL -1 year));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31 23:59:59'', INTERVAL -1 second)', date_sub('9999-12-31 23:59:59', INTERVAL -1 second));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31 23:59:00'', INTERVAL -1 minute)', date_sub('9999-12-31 23:59:00', INTERVAL -1 minute));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31 23:00:00'', INTERVAL -1 hour)', date_sub('9999-12-31 23:00:00', INTERVAL -1 hour));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(time''838:59:59'', INTERVAL -1 second)', date_sub(time'838:59:59', INTERVAL -1 second));
WARNING: time field value out of range
CONTEXT: referenced column: result
insert into test values('date_sub(time''-838:59:59'', INTERVAL 1 second)', date_sub(time'-838:59:59', INTERVAL 1 second));
WARNING: time field value out of range
CONTEXT: referenced column: result
-- 严格模式，参数不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_sub(0, INTERVAL 100 second)', date_sub(0, INTERVAL 100 second));
ERROR: Incorrect datetime value: "0"
CONTEXT: referenced column: result
insert into test values('date_sub(''10000-1-1'', INTERVAL 1 day)', date_sub('10000-1-1', INTERVAL 1 day));
ERROR: Incorrect datetime value: "10000-1-1"
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31'', INTERVAL -1 day)', date_sub('9999-12-31', INTERVAL -1 day));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31'', INTERVAL -1 month)', date_sub('9999-12-31', INTERVAL -1 month));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31'', INTERVAL -1 year)', date_sub('9999-12-31', INTERVAL -1 year));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31 23:59:59'', INTERVAL -1 second)', date_sub('9999-12-31 23:59:59', INTERVAL -1 second));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31 23:59:00'', INTERVAL -1 minute)', date_sub('9999-12-31 23:59:00', INTERVAL -1 minute));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(''9999-12-31 23:00:00'', INTERVAL -1 hour)', date_sub('9999-12-31 23:00:00', INTERVAL -1 hour));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('date_sub(time''838:59:59'', INTERVAL -1 second)', date_sub(time'838:59:59', INTERVAL -1 second));
ERROR: time field value out of range
CONTEXT: referenced column: result
insert into test values('date_sub(time''-838:59:59'', INTERVAL 1 second)', date_sub(time'-838:59:59', INTERVAL 1 second));
ERROR: time field value out of range
CONTEXT: referenced column: result
-- 结果小于'0001-1-1 00:00:00'时，结果中日期部分置为'0000-00-00'，时间部分为相应值。
insert into test values('date_sub(''0001-1-1'', INTERVAL 1 day)', date_sub('0001-1-1', INTERVAL 1 day));
insert into test values('date_sub(''0001-1-1 00:00:00'', INTERVAL 1 second)', date_sub('0001-1-1 00:00:00', INTERVAL 1 second));
-- 结果超越边界，严格模式报错，非严格模式报warning，返回NULL。
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_sub(''00010101'', INTERVAL -367 day)', date_sub('00010101', INTERVAL 367 day));
ERROR: date/time field overflow
CONTEXT: referenced column: result
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('date_sub(''00010101'', INTERVAL -367 day)', date_sub('00010101', INTERVAL 367 day));
WARNING: date/time field overflow
CONTEXT: referenced column: result
-- test addtime(datetime/time, time)
-- 严格模式或者非严格模式都有值
-- 功能
insert into test values('addtime(''00:00:00'', ''00:00:00'')', addtime('00:00:00', '00:00:00'));
insert into test values('addtime(''11:22:33'', ''20:20:20'')', addtime('11:22:33', '20:20:20'));
insert into test values('addtime(''11:22:33.999999'', ''00:00:00.000001'')', addtime('11:22:33.999999', '00:00:00.000001'));
insert into test values('addtime(''838:59:58'', ''00:00:01'')', addtime('838:59:58', '00:00:01'));
insert into test values('addtime(''838:59:59'', ''-00:00:01'')', addtime('838:59:59', '-00:00:01'));
insert into test values('addtime(''-838:59:58'', ''-00:00:01'')', addtime('-838:59:58', '-00:00:01'));
insert into test values('addtime(''-838:59:59'', ''00:00:01'')', addtime('-838:59:59', '00:00:01'));
insert into test values('addtime(''9999-12-31 23:59:59.999999'', ''00:00:00'')', addtime('9999-12-31 23:59:59.999999','00:00:00'));
insert into test values('addtime(''9999-12-31 23:59:59.999999'', ''-00:00:01'')', addtime('9999-12-31 23:59:59.999999','00:00:00'));
insert into test values('addtime(''9999-12-31 23:59:59.999999'', ''-838:59:59'')', addtime('9999-12-31 23:59:59.999999','-838:59:59'));
insert into test values('addtime(''2021-11-12 23:59:59'', ''23:59:10'')', addtime('2021-11-12 23:59:59', '23:59:10'));
insert into test values('addtime(''11:11:11'', ''11:11:11'')', addtime('11:11:11', '11:11:11'));
-- 类型
insert into test values('addtime(cast(''9999-12-31 23:59:59.999999'' as datetime(6)), ''00:00:00'')', addtime(cast('9999-12-31 23:59:59.999999' as datetime(6)),'00:00:00'));
insert into test values('addtime(time''12:12:12.999999'', ''00:00:00.000001'')', addtime(time'12:12:12.999999', '00:00:00.000001'));
insert into test values('addtime(''-8385959'', ''00:00:01'')', addtime('-8385959', '00:00:01'));
insert into test values('addtime(-8385959, ''00:00:01'')', addtime(-8385959, '00:00:01'));
insert into test values('addtime(0, ''00:00:01'')', addtime(0, '00:00:01'));
insert into test values('addtime(121112, ''10:00:00'')', addtime(121112, '10:00:00'));
-- 特异
insert into test values('addtime(null, ''00:00:01'')', addtime(null, '00:00:01'));
insert into test values('addtime(''00:00:01'', null)', addtime('00:00:01', null));
insert into test values('addtime(null, null)', addtime(null, null));
insert into test values('addtime(''12:12:12'', ''2021-11-12 12:12:12'')', addtime('12:12:12', '2021-11-12 12:12:12'));
insert into test values('addtime(''0001-1-1 00:00:00'', ''-00:00:01'')', addtime('0001-1-1 00:00:00', '-00:00:01'));
-- 非严格模式，参数或者结果不合法，报warning，返回NULL或者对应值
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('addtime(''839:00:00'', ''00:00:00'')', addtime('839:00:00', '00:00:00'));
WARNING: Truncated incorrect time value: "839:00:00"
CONTEXT: referenced column: result
insert into test values('addtime(''838:59:59'', ''00:00:01'')', addtime('838:59:59', '00:00:01'));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('addtime(''838:59:59.1'', ''00:00:00'')', addtime('838:59:59.1', '00:00:00'));
WARNING: Truncated incorrect time value: "838:59:59.1"
CONTEXT: referenced column: result
insert into test values('addtime(''-838:59:59.1'', ''00:00:00'')', addtime('-838:59:59.1', '00:00:00'));
WARNING: Truncated incorrect time value: "-838:59:59.1"
CONTEXT: referenced column: result
insert into test values('addtime(''-839:00:00'', ''00:00:00'')', addtime('-839:00:00', '00:00:00'));
WARNING: Truncated incorrect time value: "-839:00:00"
CONTEXT: referenced column: result
insert into test values('addtime(''-838:59:59'', ''-00:00:01'')', addtime('-838:59:59', '-00:00:01'));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('addtime(''9999-12-31 23:59:59'', ''00:00:01'')', addtime('9999-12-31 23:59:59', '00:00:01'));
WARNING: date/time field overflow
CONTEXT: referenced column: result
insert into test values('addtime(''10000-1-1 00:00:00'', ''00:00:00'')', addtime('10000-1-1 00:00:00', '00:00:00'));
WARNING: Truncated incorrect time value: "10000-1-1 00:00:00"
CONTEXT: referenced column: result
insert into test values('addtime(''2022-01-01'', ''11:11:11'')', addtime('2022-01-01', '11:11:11'));
WARNING: Truncated incorrect time value: "2022-01-01"
CONTEXT: referenced column: result
insert into test values('addtime(99991231, ''10:00:00'')', addtime(99991231, '10:00:00'));
WARNING: Truncated incorrect time value: "99991231"
CONTEXT: referenced column: result
WARNING: date/time field overflow
CONTEXT: referenced column: result
-- 严格模式，参数或结果不合法，抛出错误
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test values('addtime(''839:00:00'', ''00:00:00'')', addtime('839:00:00', '00:00:00'));
ERROR: Truncated incorrect time value: "839:00:00"
CONTEXT: referenced column: result
insert into test values('addtime(''838:59:59'', ''00:00:01'')', addtime('838:59:59', '00:00:01'));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('addtime(''838:59:59.1'', ''00:00:00'')', addtime('838:59:59.1', '00:00:00'));
ERROR: Truncated incorrect time value: "838:59:59.1"
CONTEXT: referenced column: result
insert into test values('addtime(''-838:59:59.1'', ''00:00:00'')', addtime('-838:59:59.1', '00:00:00'));
ERROR: Truncated incorrect time value: "-838:59:59.1"
CONTEXT: referenced column: result
insert into test values('addtime(''-839:00:00'', ''00:00:00'')', addtime('-839:00:00', '00:00:00'));
ERROR: Truncated incorrect time value: "-839:00:00"
CONTEXT: referenced column: result
insert into test values('addtime(''-838:59:59'', ''-00:00:01'')', addtime('-838:59:59', '-00:00:01'));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('addtime(''9999-12-31 23:59:59'', ''00:00:01'')', addtime('9999-12-31 23:59:59', '00:00:01'));
ERROR: date/time field overflow
CONTEXT: referenced column: result
insert into test values('addtime(''10000-1-1 00:00:00'', ''00:00:00'')', addtime('10000-1-1 00:00:00', '00:00:00'));
ERROR: Truncated incorrect time value: "10000-1-1 00:00:00"
CONTEXT: referenced column: result
insert into test values('addtime(''2022-01-01'', ''11:11:11'')', addtime('2022-01-01', '11:11:11'));
ERROR: Truncated incorrect time value: "2022-01-01"
CONTEXT: referenced column: result
insert into test values('addtime(99991231, ''10:00:00'')', addtime(99991231, '10:00:00'));
ERROR: Truncated incorrect time value: "99991231"
CONTEXT: referenced column: result
select * from test order by funcname;
funcname | result
--------------------------------------------------------------------------------------------------+----------------------------
adddate('0001-01-01 12:12:12',INTERVAL 1 hour) | 0001-01-01 13:12:12
adddate('0001-01-01 12:12:12',INTERVAL 1 minute) | 0001-01-01 12:13:12
adddate('0001-01-01 12:12:12',INTERVAL 1 second) | 0001-01-01 12:12:13
adddate('0001-01-01',INTERVAL 1 day) | 0001-01-02
adddate('0001-01-01',INTERVAL 1 hour) | 0001-01-01 01:00:00
adddate('0001-01-01',INTERVAL 1 minute) | 0001-01-01 00:01:00
adddate('0001-01-01',INTERVAL 1 month) | 0001-02-01
adddate('0001-01-01',INTERVAL 1 second) | 0001-01-01 00:00:01
adddate('0001-01-01',INTERVAL 1 year) | 0002-01-01
adddate('0001-1-1 00:00:00', INTERVAL -1 second) | 0000-00-00 23:59:59
adddate('0001-1-1', -1) | 0000-00-00
adddate('0001-1-1', INTERVAL -1 day) | 0000-00-00
adddate('0001-12-31',INTERVAL 1 day) | 0002-01-01
adddate('0001-12-31',INTERVAL 1 month) | 0002-01-31
adddate('00010101', -367) |
adddate('10000-1-1', INTERVAL -1 day) |
adddate('11:11:11', 2) | 2011-11-13
adddate('11:11:11', INTERVAL 2 day) | 2011-11-13
adddate('11:11:11', INTERVAL 2 second) | 2011-11-11 00:00:02
adddate('1999-12-31 23:00:00',INTERVAL 1 hour) | 2000-01-01 00:00:00
adddate('1999-12-31 23:59:00',INTERVAL 1 minute) | 2000-01-01 00:00:00
adddate('1999-12-31 23:59:59',INTERVAL 1 second) | 2000-01-01 00:00:00
adddate('1999-12-31',INTERVAL 1 day) | 2000-01-01
adddate('1999-12-31',INTERVAL 1 year) | 2000-12-31
adddate('2021-11-12', -1) | 2021-11-11
adddate('2021-11-12', 1) | 2021-11-13
adddate('2021-11-12', null) |
adddate('2021-12-31',INTERVAL 1 day) | 2022-01-01
adddate('9998-12-31',INTERVAL 1 day) | 9999-01-01
adddate('9999-12-31 12:12:12',INTERVAL -1 hour) | 9999-12-31 11:12:12
adddate('9999-12-31 12:12:12',INTERVAL -1 minute) | 9999-12-31 12:11:12
adddate('9999-12-31 12:12:12',INTERVAL -1 second) | 9999-12-31 12:12:11
adddate('9999-12-31 23:00:00', INTERVAL 1 hour) |
adddate('9999-12-31 23:59:00', INTERVAL 1 minute) |
adddate('9999-12-31 23:59:59', INTERVAL 1 second) |
adddate('9999-12-31', INTERVAL 1 day) |
adddate('9999-12-31', INTERVAL 1 month) |
adddate('9999-12-31', INTERVAL 1 year) |
adddate('9999-12-31',INTERVAL -1 day) | 9999-12-30
adddate('9999-12-31',INTERVAL -1 hour) | 9999-12-30 23:00:00
adddate('9999-12-31',INTERVAL -1 minute) | 9999-12-30 23:59:00
adddate('9999-12-31',INTERVAL -1 month) | 9999-11-30
adddate('9999-12-31',INTERVAL -1 second) | 9999-12-30 23:59:59
adddate('9999-12-31',INTERVAL -1 year) | 9998-12-31
adddate(0, INTERVAL 100 second) |
adddate(101, 1) | 2000-01-02
adddate(101, INTERVAL 1 year) | 2001-01-01
adddate(101, INTERVAL 100 second) | 2000-01-01 00:01:40
adddate(cast('9999-12-31 12:12:12' as datetime),INTERVAL -1 second) | 9999-12-31 12:12:11
adddate(date'0001-01-01',INTERVAL 1 year) | 0002-01-01
adddate(null, INTERVAL 1 day) |
adddate(null, null) |
adddate(time'-838:59:59', INTERVAL -1 second) |
adddate(time'12:12:12', -1) | -11:47:48
adddate(time'12:12:12', 1) | 36:12:12
adddate(time'12:12:12', INTERVAL -1 day) | -11:47:48
adddate(time'12:12:12', INTERVAL -1 hour) | 11:12:12
adddate(time'12:12:12', INTERVAL -1 minute) | 12:11:12
adddate(time'12:12:12', INTERVAL -1 second) | 12:12:11
adddate(time'12:12:12', INTERVAL 1 day) | 36:12:12
adddate(time'12:12:12', INTERVAL 1 hour) | 13:12:12
adddate(time'12:12:12', INTERVAL 1 minute) | 12:13:12
adddate(time'12:12:12', INTERVAL 1 second) | 12:12:13
adddate(time'838:59:59', INTERVAL -1 second) | 838:59:58
adddate(time'838:59:59', INTERVAL 1 second) |
addtime('-8385959', '00:00:01') | -838:59:58
addtime('-838:59:58', '-00:00:01') | -838:59:59
addtime('-838:59:59', '-00:00:01') | -838:59:59
addtime('-838:59:59', '00:00:01') | -838:59:58
addtime('-838:59:59.1', '00:00:00') | -838:59:59
addtime('-839:00:00', '00:00:00') | -838:59:59
addtime('0001-1-1 00:00:00', '-00:00:01') |
addtime('00:00:00', '00:00:00') | 00:00:00
addtime('00:00:01', null) |
addtime('10000-1-1 00:00:00', '00:00:00') |
addtime('11:11:11', '11:11:11') | 22:22:22
addtime('11:22:33', '20:20:20') | 31:42:53
addtime('11:22:33.999999', '00:00:00.000001') | 11:22:34
addtime('12:12:12', '2021-11-12 12:12:12') |
addtime('2021-11-12 23:59:59', '23:59:10') | 2021-11-13 23:59:09
addtime('2022-01-01', '11:11:11') | 11:31:33
addtime('838:59:58', '00:00:01') | 838:59:59
addtime('838:59:59', '-00:00:01') | 838:59:58
addtime('838:59:59', '00:00:01') | 838:59:59
addtime('838:59:59.1', '00:00:00') | 838:59:59
addtime('839:00:00', '00:00:00') | 838:59:59
addtime('9999-12-31 23:59:59', '00:00:01') |
addtime('9999-12-31 23:59:59.999999', '-00:00:01') | 9999-12-31 23:59:59.999999
addtime('9999-12-31 23:59:59.999999', '-838:59:59') | 9999-11-27 01:00:00.999999
addtime('9999-12-31 23:59:59.999999', '00:00:00') | 9999-12-31 23:59:59.999999
addtime(-8385959, '00:00:01') | -838:59:58
addtime(0, '00:00:01') | 00:00:01
addtime(121112, '10:00:00') | 22:11:12
addtime(99991231, '10:00:00') | 838:59:59
addtime(cast('9999-12-31 23:59:59.999999' as datetime(6)), '00:00:00') | 9999-12-31 23:59:59.999999
addtime(null, '00:00:01') |
addtime(null, null) |
addtime(time'12:12:12.999999', '00:00:00.000001') | 12:12:13
convert_tz('10000-1-1 10:10:10', '+1:00', '+0:00') |
convert_tz('1234-1-1 00:00:00', 'MET', 'GMT') | 1234-01-01 00:00:00
convert_tz('1970-1-1 00:00:01.000000', '+1:00', '+0:00') | 1970-01-01 00:00:01
convert_tz('1970-1-1 00:00:01.000000', '-10:00', '+0:00') | 1970-01-01 10:00:01
convert_tz('1970-1-1 00:00:01.000000', '-1:00', '+0:00') | 1970-01-01 01:00:01
convert_tz('1970-1-1 00:00:01.000000', '-1:00', 'GMT') | 1970-01-01 01:00:01
convert_tz('1970-1-1 00:00:01.000000', '-2:00', '+0:00') | 1970-01-01 02:00:01
convert_tz('1970-1-1 00:00:01.000000', 'CCT', 'GMT') | 1970-01-01 00:00:01
convert_tz('1970-1-1 00:00:01.000000', 'CDT', 'GMT') | 1970-01-01 05:00:01
convert_tz('1970-1-1 00:00:01.000000', 'EDT', 'GMT') | 1970-01-01 04:00:01
convert_tz('1970-1-1 00:00:01.000000', 'EST', 'GMT') | 1970-01-01 05:00:01
convert_tz('1970-1-1 00:00:01.000000', 'GMT', '+3:00') | 1970-01-01 03:00:01
convert_tz('1970-1-1 00:00:01.000000', 'GMT', 'GMT') | 1970-01-01 00:00:01
convert_tz('1970-1-1 00:00:01.000000', 'MDT', 'GMT') | 1970-01-01 06:00:01
convert_tz('1970-1-1 00:00:01.000000', 'MET', 'GMT') | 1970-01-01 00:00:01
convert_tz('1970-1-1 00:00:01.000000', 'MST', 'GMT') | 1970-01-01 07:00:01
convert_tz('2021-11-12 12:12:12', '+0:00', '-13:00') |
convert_tz('2021-11-12 12:12:12', '+13:01', '+0:00') |
convert_tz('2021-11-12 12:12:12', '+1:00', 'abc') |
convert_tz('2021-11-12 12:12:12', '+1:00', null) |
convert_tz('2021-11-12 12:12:12', 'abc', '+0:00') |
convert_tz('2021-11-12 12:12:12', null, '+0:00') |
convert_tz('2038-01-19 03:14:07.999999', '+10:00', '+0:00') | 2038-01-18 17:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', '+1:00', '+0:00') | 2038-01-19 02:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', '+2:00', '+0:00') | 2038-01-19 01:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', '+2:00', 'GMT') | 2038-01-19 01:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', '-1:00', '+0:00') | 1970-01-01 00:00:01
convert_tz('2038-01-19 03:14:07.999999', 'CCT', 'GMT') | 2038-01-18 19:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', 'EDT', 'GMT') | 2038-01-19 03:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', 'GMT', '+7:00') | 2038-01-19 10:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', 'JST', 'GMT') | 2038-01-18 18:14:07.999999
convert_tz('2038-01-19 03:14:07.999999', 'KST', 'GMT') | 2038-01-18 18:14:07.999999
convert_tz(101, 'GMT', '+7:00') | 2000-01-01 07:00:00
convert_tz(null, '+1:00', '+0:00') |
--?.*
datediff('0000-01-01', '9999-12-31') | -3652424
datediff('10000-01-01', '2021-11-12') |
datediff('2021-01-01', '2021-12-31') | -364
datediff('2021-11-12', '2021-11-13') | -1
datediff('2021-11-12', 101) | 7986
datediff('2021-11-12', 101) | 7986
datediff('2021-11-12', null) |
datediff('2021-12-31 12:12:12', '2022-01-01 10:10:10') | -1
datediff('2021-12-31', '2022-01-01') | -1
--?.*
datediff('9999-12-31', '0000-01-01') | 3652424
datediff(100000101, 20211112) |
datediff(101, '2021-11-12') | -7986
datediff(20211112, 20211113) | -1
datediff(99991231235959.9999995, 20211112) |
datediff(9999999999999999999999, 20211112) |
datediff(cast('0021-12-31 12:12:12' as date), cast('2022-01-01 10:10:10' as date)) | -730486
datediff(cast('2021-12-31 12:12:12' as datetime), cast('2022-01-01 10:10:10' as datetime)) | -1
datediff(date'2021-11-12', date'2021-11-13') | 0
datediff(null, '2021-11-12') |
datediff(null, null) |
--?.*
datediff(time'10:10:10', time'10:10:10') | 0
date_add('0001-01-01 12:12:12',INTERVAL 1 hour) | 0001-01-01 13:12:12
date_add('0001-01-01 12:12:12',INTERVAL 1 minute) | 0001-01-01 12:13:12
date_add('0001-01-01 12:12:12',INTERVAL 1 second) | 0001-01-01 12:12:13
date_add('0001-01-01',INTERVAL 1 day) | 0001-01-02
date_add('0001-01-01',INTERVAL 1 hour) | 0001-01-01 01:00:00
date_add('0001-01-01',INTERVAL 1 minute) | 0001-01-01 00:01:00
date_add('0001-01-01',INTERVAL 1 month) | 0001-02-01
date_add('0001-01-01',INTERVAL 1 second) | 0001-01-01 00:00:01
date_add('0001-01-01',INTERVAL 1 year) | 0002-01-01
date_add('0001-1-1 00:00:00', INTERVAL -1 second) | 0000-00-00 23:59:59
date_add('0001-1-1', INTERVAL -1 day) | 0000-00-00
date_add('0001-12-31',INTERVAL 1 day) | 0002-01-01
date_add('0001-12-31',INTERVAL 1 month) | 0002-01-31
date_add('00010101', INTERVAL -367 day) |
date_add('10000-1-1', INTERVAL -1 day) |
date_add('1999-12-31 23:00:00',INTERVAL 1 hour) | 2000-01-01 00:00:00
date_add('1999-12-31 23:59:00',INTERVAL 1 minute) | 2000-01-01 00:00:00
date_add('1999-12-31 23:59:59',INTERVAL 1 second) | 2000-01-01 00:00:00
date_add('1999-12-31',INTERVAL 1 day) | 2000-01-01
date_add('1999-12-31',INTERVAL 1 year) | 2000-12-31
date_add('2021-12-31',INTERVAL 1 day) | 2022-01-01
date_add('9998-12-31',INTERVAL 1 day) | 9999-01-01
date_add('9999-12-31 12:12:12',INTERVAL -1 hour) | 9999-12-31 11:12:12
date_add('9999-12-31 12:12:12',INTERVAL -1 minute) | 9999-12-31 12:11:12
date_add('9999-12-31 12:12:12',INTERVAL -1 second) | 9999-12-31 12:12:11
date_add('9999-12-31 23:00:00', INTERVAL 1 hour) |
date_add('9999-12-31 23:59:00', INTERVAL 1 minute) |
date_add('9999-12-31 23:59:59', INTERVAL 1 second) |
date_add('9999-12-31', INTERVAL 1 day) |
date_add('9999-12-31', INTERVAL 1 month) |
date_add('9999-12-31', INTERVAL 1 year) |
date_add('9999-12-31',INTERVAL -1 day) | 9999-12-30
date_add('9999-12-31',INTERVAL -1 hour) | 9999-12-30 23:00:00
date_add('9999-12-31',INTERVAL -1 minute) | 9999-12-30 23:59:00
date_add('9999-12-31',INTERVAL -1 month) | 9999-11-30
date_add('9999-12-31',INTERVAL -1 second) | 9999-12-30 23:59:59
date_add('9999-12-31',INTERVAL -1 year) | 9998-12-31
date_add(0, INTERVAL 100 second) |
date_add(101, INTERVAL 1 year) | 2001-01-01
date_add(101, INTERVAL 100 second) | 2000-01-01 00:01:40
date_add(cast('9999-12-31 12:12:12' as datetime),INTERVAL -1 second) | 9999-12-31 12:12:11
date_add(date'0001-01-01',INTERVAL 1 year) | 0002-01-01
date_add(null, INTERVAL 1 day) |
date_add(time'-838:59:59', INTERVAL -1 second) |
--? date_add(time'12:12:12', INTERVAL -1 day) | .* 12:12:12
date_add(time'12:12:12', INTERVAL -1 hour) | 11:12:12
date_add(time'12:12:12', INTERVAL -1 minute) | 12:11:12
date_add(time'12:12:12', INTERVAL -1 second) | 12:12:11
--? date_add(time'12:12:12', INTERVAL 1 day) | .* 12:12:12
date_add(time'12:12:12', INTERVAL 1 hour) | 13:12:12
date_add(time'12:12:12', INTERVAL 1 minute) | 12:13:12
date_add(time'12:12:12', INTERVAL 1 second) | 12:12:13
date_add(time'838:59:59', INTERVAL -1 second) | 838:59:58
date_add(time'838:59:59', INTERVAL 1 second) |
date_sub('0001-01-01 12:12:12',INTERVAL -1 hour) | 0001-01-01 13:12:12
date_sub('0001-01-01 12:12:12',INTERVAL -1 minute) | 0001-01-01 12:13:12
date_sub('0001-01-01 12:12:12',INTERVAL -1 second) | 0001-01-01 12:12:13
date_sub('0001-01-01',INTERVAL -1 day) | 0001-01-02
date_sub('0001-01-01',INTERVAL -1 hour) | 0001-01-01 01:00:00
date_sub('0001-01-01',INTERVAL -1 minute) | 0001-01-01 00:01:00
date_sub('0001-01-01',INTERVAL -1 month) | 0001-02-01
date_sub('0001-01-01',INTERVAL -1 second) | 0001-01-01 00:00:01
date_sub('0001-01-01',INTERVAL -1 year) | 0002-01-01
date_sub('0001-1-1 00:00:00', INTERVAL 1 second) | 0000-00-00 23:59:59
date_sub('0001-1-1', INTERVAL 1 day) | 0000-00-00
date_sub('0001-12-31',INTERVAL -1 day) | 0002-01-01
date_sub('0001-12-31',INTERVAL -1 month) | 0002-01-31
date_sub('00010101', INTERVAL -367 day) |
date_sub('10000-1-1', INTERVAL 1 day) |
date_sub('1999-12-31 23:00:00',INTERVAL -1 hour) | 2000-01-01 00:00:00
date_sub('1999-12-31 23:59:00',INTERVAL -1 minute) | 2000-01-01 00:00:00
date_sub('1999-12-31 23:59:59',INTERVAL -1 second) | 2000-01-01 00:00:00
date_sub('1999-12-31',INTERVAL -1 day) | 2000-01-01
date_sub('1999-12-31',INTERVAL -1 year) | 2000-12-31
date_sub('2021-12-31',INTERVAL -1 day) | 2022-01-01
date_sub('9998-12-31',INTERVAL -1 day) | 9999-01-01
date_sub('9999-12-31 12:12:12',INTERVAL 1 hour) | 9999-12-31 11:12:12
date_sub('9999-12-31 12:12:12',INTERVAL 1 minute) | 9999-12-31 12:11:12
date_sub('9999-12-31 12:12:12',INTERVAL 1 second) | 9999-12-31 12:12:11
date_sub('9999-12-31 23:00:00', INTERVAL -1 hour) |
date_sub('9999-12-31 23:59:00', INTERVAL -1 minute) |
date_sub('9999-12-31 23:59:59', INTERVAL -1 second) |
date_sub('9999-12-31', INTERVAL -1 day) |
date_sub('9999-12-31', INTERVAL -1 month) |
date_sub('9999-12-31', INTERVAL -1 year) |
date_sub('9999-12-31',INTERVAL 1 day) | 9999-12-30
date_sub('9999-12-31',INTERVAL 1 hour) | 9999-12-30 23:00:00
date_sub('9999-12-31',INTERVAL 1 minute) | 9999-12-30 23:59:00
date_sub('9999-12-31',INTERVAL 1 month) | 9999-11-30
date_sub('9999-12-31',INTERVAL 1 second) | 9999-12-30 23:59:59
date_sub('9999-12-31',INTERVAL 1 year) | 9998-12-31
date_sub(0, INTERVAL 100 second) |
date_sub(101, INTERVAL 1 year) | 1999-01-01
date_sub(101, INTERVAL 100 second) | 1999-12-31 23:58:20
date_sub(cast('9999-12-31 12:12:12' as datetime),INTERVAL -1 second) | 9999-12-31 12:12:13
date_sub(date'0001-01-01',INTERVAL -1 year) | 0002-01-01
date_sub(null, INTERVAL 1 day) |
date_sub(time'-838:59:59', INTERVAL 1 second) |
--? date_sub(time'12:12:12', INTERVAL -1 day) | .* 12:12:12
date_sub(time'12:12:12', INTERVAL -1 hour) | 13:12:12
date_sub(time'12:12:12', INTERVAL -1 minute) | 12:13:12
date_sub(time'12:12:12', INTERVAL -1 second) | 12:12:13
--? date_sub(time'12:12:12', INTERVAL 1 day) | .* 12:12:12
date_sub(time'12:12:12', INTERVAL 1 hour) | 11:12:12
date_sub(time'12:12:12', INTERVAL 1 minute) | 12:11:12
date_sub(time'12:12:12', INTERVAL 1 second) | 12:12:11
date_sub(time'838:59:59', INTERVAL -1 second) |
date_sub(time'838:59:59', INTERVAL 1 second) | 838:59:58
from_days('1111111111111111111') | 0000-00-00
from_days('2000000') | 5475-10-25
from_days('3652499') | 10000-03-15
from_days('366') | 0001-01-01
from_days(-1) | 0000-00-00
from_days(0) | 0000-00-00
from_days(1111111111111111111) | 0000-00-00
from_days(2000000) | 5475-10-25
from_days(365) | 0000-00-00
from_days(3652499) | 10000-03-15
from_days(366) | 0001-01-01
from_days(null) |
timestampdiff(day, '0000-1-1', '9999-12-31') | 3652423
timestampdiff(day, '9999-12-31', '0000-12-31') | -3652058
timestampdiff(day, time'-838:59:59', time'838:59:59') | 69
timestampdiff(hour, '0000-1-1', '0000-1-2') | 24
timestampdiff(hour, '0000-1-1', '9999-12-31') | -87649415
timestampdiff(hour, time'-838:59:59', time'838:59:59') | 1677
timestampdiff(microsecond, '0000-1-1 00:00:00', '0000-1-2') | 86400000000
timestampdiff(microsecond, '0000-1-1', '9999-12-31') | 315569433257000000
timestampdiff(microsecond, '10000-1-1', '2021-11-12') |
timestampdiff(microsecond, '2021-11-12', '10000-1-1') |
timestampdiff(microsecond, '20211112121212.999999', '20220912111111.999999') | 26261939000000
timestampdiff(microsecond, '20211112121212.999999', 101) | -690034332999999
--?.*
--?.*
timestampdiff(microsecond, 'abc', '2021-11-12') |
timestampdiff(microsecond, 101, '20211112121212.999999') | 690034332999999
timestampdiff(microsecond, 101, 99991231235959.999999) | 252455615999999999
timestampdiff(microsecond, 99991231235959.999999, 101) | -252455615999999999
--?.*
--?.*
timestampdiff(microsecond, time'-838:59:59', time'838:59:59') | 6040798000000
--?.*
--?.*
--?.*
timestampdiff(minute, '0000-1-1 00:00:00', '0000-1-2') | 1440
timestampdiff(minute, '0000-1-1', '9999-12-31') | 5259490554
timestampdiff(minute, time'-838:59:59', time'838:59:59') | 100679
timestampdiff(month, '0000-1-1', '9999-12-31') | 119999
timestampdiff(month, '9999-12-31', '0000-12-31') | -119987
timestampdiff(month, time'-838:59:59', time'838:59:59') | 2
timestampdiff(quarter, '0000-1-1', '9999-12-31') | 39999
timestampdiff(quarter, '9999-12-31', '0000-12-31') | -39995
timestampdiff(quarter, time'-838:59:59', time'838:59:59') | 0
timestampdiff(second, '0000-1-1 00:00:00', '0000-1-2') | 86400
timestampdiff(second, '0000-1-1', '9999-12-31') | 315569433257
timestampdiff(second, time'-838:59:59', time'838:59:59') | 6040798
timestampdiff(week, '0000-1-1', '9999-12-31') | 521774
timestampdiff(week, '9999-12-31', '0000-12-31') | -521722
timestampdiff(week, '9999-12-31', null) |
timestampdiff(week, null, '9999-12-31') |
timestampdiff(week, null, null) |
timestampdiff(week, time'-838:59:59', time'838:59:59') | 9
timestampdiff(year, '0000-1-1', '9999-12-31') | 9999
timestampdiff(year, '9999-12-31', '0000-12-31') | -9998
timestampdiff(year, time'-838:59:59', time'838:59:59') | 0
(321 rows)
drop table test;
-- test timediff(date,unknown)
select timediff(date'2022-01-01','2022-01-02');
timediff
-----------
-24:00:00
(1 row)
select timediff('2022-01-01',date'2022-01-02');
timediff
-----------
-24:00:00
(1 row)
-- test timediff unknown入参结果错误
select timediff(date'2022-12-12', 'asdcf');
WARNING: invalid input syntax for type date: "asdcf"
CONTEXT: referenced column: timediff
timediff
----------
(1 row)
select timediff(time'11:11:11', 'asdcf');
WARNING: Truncated incorrect time value: "asdcf"
CONTEXT: referenced column: timediff
timediff
----------
11:11:11
(1 row)
select timediff('2022-12-12', '00:00:00');
WARNING: invalid input syntax for type time: "2022-12-12"
CONTEXT: referenced column: timediff
timediff
----------
00:20:22
(1 row)
select timediff('0000-00-00', time'00:00:01');
WARNING: Truncated incorrect time value: "0000-00-00"
CONTEXT: referenced column: timediff
timediff
-----------
-00:00:01
(1 row)
select timediff(time'00:00:01', '0000-00-00');
WARNING: Truncated incorrect time value: "0000-00-00"
CONTEXT: referenced column: timediff
timediff
----------
00:00:01
(1 row)
select timediff('0000-00-00', '00:00:00');
WARNING: invalid input syntax for type time: "0000-00-00"
CONTEXT: referenced column: timediff
timediff
----------
00:00:00
(1 row)
select timediff('00:00:00', '0000-00-00');
WARNING: invalid input syntax for type time: "0000-00-00"
CONTEXT: referenced column: timediff
timediff
----------
00:00:00
(1 row)
select timediff('0000-00-00', '0000-00-00');
WARNING: invalid input syntax for type time: "0000-00-00"
CONTEXT: referenced column: timediff
WARNING: invalid input syntax for type time: "0000-00-00"
CONTEXT: referenced column: timediff
timediff
----------
00:00:00
(1 row)
-- 测试time_to_sec异常边界
select time_to_sec(8385960);
WARNING: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(8385959);
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(-8385960);
WARNING: Truncated incorrect time value: "-8385960"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-8385959);
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(10000101000000);
time_to_sec
-------------
0
(1 row)
select time_to_sec(10000100000000);
WARNING: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(100000101000000);
WARNING: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(99999999999999);
WARNING: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(99990100000000);
WARNING: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(20220101010101);
time_to_sec
-------------
3661
(1 row)
select time_to_sec(99991231235959);
time_to_sec
-------------
86399
(1 row)
select time_to_sec(99991231240000);
WARNING: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(10000101000001);
time_to_sec
-------------
1
(1 row)
select time_to_sec(10000101000059);
time_to_sec
-------------
59
(1 row)
select time_to_sec(10000101000060);
WARNING: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(0);
time_to_sec
-------------
0
(1 row)
select time_to_sec(100000000000000);
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec('8385960');
WARNING: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec('8385959');
time_to_sec
-------------
3020399
(1 row)
select time_to_sec('-8385960');
WARNING: Truncated incorrect time value: "-8385960"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec('-8385959');
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec('10000101000000');
time_to_sec
-------------
0
(1 row)
select time_to_sec('10000100000000');
time_to_sec
-------------
0
(1 row)
select time_to_sec('100000101000000');
WARNING: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
36000
(1 row)
select time_to_sec('99999999999999');
WARNING: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec('99990100000000');
time_to_sec
-------------
0
(1 row)
select time_to_sec('20220101010101');
time_to_sec
-------------
3661
(1 row)
select time_to_sec('99991231235959');
time_to_sec
-------------
86399
(1 row)
select time_to_sec('99991231240000');
WARNING: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec('10000101000001');
time_to_sec
-------------
1
(1 row)
select time_to_sec('10000101000059');
time_to_sec
-------------
59
(1 row)
select time_to_sec('10000101000060');
WARNING: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec('0');
time_to_sec
-------------
0
(1 row)
select time_to_sec('100000000000000');
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
0
(1 row)
select time_to_sec(8385960.11);
WARNING: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(8385959.11);
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(-8385960.11);
WARNING: Truncated incorrect time value: "-8385960.11"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-8385959.11);
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(10000101000000.11);
time_to_sec
-------------
0
(1 row)
select time_to_sec(10000100000000.11);
WARNING: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(100000101000000.11);
WARNING: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(99999999999999.11);
WARNING: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(99990100000000.11);
WARNING: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(20220101010101.11);
time_to_sec
-------------
3661
(1 row)
select time_to_sec(99991231235959.11);
time_to_sec
-------------
86399
(1 row)
select time_to_sec(99991231240000.11);
WARNING: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(10000101000001.11);
time_to_sec
-------------
1
(1 row)
select time_to_sec(10000101000059.11);
time_to_sec
-------------
59
(1 row)
select time_to_sec(10000101000060.11);
WARNING: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(0.11);
time_to_sec
-------------
0
(1 row)
select time_to_sec(100000000000000.11);
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(10000101000001.99999);
time_to_sec
-------------
1
(1 row)
select time_to_sec(10000101000001.999999);
time_to_sec
-------------
1
(1 row)
select time_to_sec(10000101000001.9999994);
time_to_sec
-------------
1
(1 row)
select time_to_sec(10000101000001.9999995);
time_to_sec
-------------
2
(1 row)
select time_to_sec(10000101000001.999999999);
time_to_sec
-------------
2
(1 row)
select time_to_sec(8385959.99999);
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(8385959.999999);
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(8385959.9999994);
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(8385959.9999995);
WARNING: Truncated incorrect time value: "8385959.9999995"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(8385959.999999999);
WARNING: Truncated incorrect time value: "8385959.999999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
3020399
(1 row)
select time_to_sec(-10000101000001.99999);
WARNING: Truncated incorrect time value: "-10000101000001.99999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-10000101000001.999999);
WARNING: Truncated incorrect time value: "-10000101000001.999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-10000101000001.999994);
WARNING: Truncated incorrect time value: "-10000101000001.999994"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-10000101000001.999995);
WARNING: Truncated incorrect time value: "-10000101000001.999995"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-10000101000001.999999999);
WARNING: Truncated incorrect time value: "-10000101000001.999999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
select time_to_sec(-8385959.99999);
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(-8385959.999999);
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(-8385959.9999994);
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(-8385959.9999995);
WARNING: Truncated incorrect time value: "-8385959.9999995"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(-8385959.999999999);
WARNING: Truncated incorrect time value: "-8385959.999999999"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
-3020399
(1 row)
select time_to_sec(-10000101000001);
WARNING: Truncated incorrect time value: "-10000101000001"
CONTEXT: referenced column: time_to_sec
time_to_sec
-------------
(1 row)
-- 严格模式 insert
create table test2(funcname text, result text);
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
insert into test2 values ('time_to_sec(100000000000000)', time_to_sec(100000000000000));
ERROR: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(10000100000000)', time_to_sec(10000100000000));
ERROR: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99990100000000)', time_to_sec(99990100000000));
ERROR: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''100000000000000'')', time_to_sec('100000000000000'));
ERROR: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''10000100000000'')', time_to_sec('10000100000000'));
insert into test2 values ('time_to_sec(''99990100000000'')', time_to_sec('99990100000000'));
insert into test2 values ('time_to_sec(''8385960'')', time_to_sec('8385960'));
ERROR: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''8385959'')', time_to_sec('8385959'));
insert into test2 values ('time_to_sec(''-8385960'')', time_to_sec('-8385960'));
ERROR: Truncated incorrect time value: "-8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''-8385959'')', time_to_sec('-8385959'));
insert into test2 values ('time_to_sec(''10000101000000'')', time_to_sec('10000101000000'));
insert into test2 values ('time_to_sec(''10000100000000'')', time_to_sec('10000100000000'));
insert into test2 values ('time_to_sec(''100000101000000'')', time_to_sec('100000101000000'));
ERROR: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''99999999999999'')', time_to_sec('99999999999999'));
ERROR: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''99990100000000'')', time_to_sec('99990100000000'));
insert into test2 values ('time_to_sec(''20220101010101'')', time_to_sec('20220101010101'));
insert into test2 values ('time_to_sec(''99991231235959'')', time_to_sec('99991231235959'));
insert into test2 values ('time_to_sec(''99991231240000'')', time_to_sec('99991231240000'));
ERROR: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''10000101000001'')', time_to_sec('10000101000001'));
insert into test2 values ('time_to_sec(''10000101000059'')', time_to_sec('10000101000059'));
insert into test2 values ('time_to_sec(''10000101000060'')', time_to_sec('10000101000060'));
ERROR: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''0'')', time_to_sec('0'));
insert into test2 values ('time_to_sec(''100000000000000'')', time_to_sec('100000000000000'));
ERROR: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385960)', time_to_sec(8385960));
ERROR: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385959)', time_to_sec(8385959));
insert into test2 values ('time_to_sec(-8385960)', time_to_sec(-8385960));
ERROR: Truncated incorrect time value: "-8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(-8385959)', time_to_sec(-8385959));
insert into test2 values ('time_to_sec(10000101000000)', time_to_sec(10000101000000));
insert into test2 values ('time_to_sec(10000100000000)', time_to_sec(10000100000000));
ERROR: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(100000101000000)', time_to_sec(100000101000000));
ERROR: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99999999999999)', time_to_sec(99999999999999));
ERROR: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99990100000000)', time_to_sec(99990100000000));
ERROR: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(20220101010101)', time_to_sec(20220101010101));
insert into test2 values ('time_to_sec(99991231235959)', time_to_sec(99991231235959));
insert into test2 values ('time_to_sec(99991231240000)', time_to_sec(99991231240000));
ERROR: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(10000101000001)', time_to_sec(10000101000001));
insert into test2 values ('time_to_sec(10000101000059)', time_to_sec(10000101000059));
insert into test2 values ('time_to_sec(10000101000060)', time_to_sec(10000101000060));
ERROR: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(0)', time_to_sec(0));
insert into test2 values ('time_to_sec(100000000000000)', time_to_sec(100000000000000));
ERROR: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385960.11)', time_to_sec(8385960.11));
ERROR: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385959.11)', time_to_sec(8385959.11));
insert into test2 values ('time_to_sec(-8385960.11)', time_to_sec(-8385960.11));
ERROR: Truncated incorrect time value: "-8385960.11"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(-8385959.11)', time_to_sec(-8385959.11));
insert into test2 values ('time_to_sec(10000101000000.11)', time_to_sec(10000101000000.11));
insert into test2 values ('time_to_sec(10000100000000.11)', time_to_sec(10000100000000.11));
ERROR: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(100000101000000.11)', time_to_sec(100000101000000.11));
ERROR: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99999999999999.11)', time_to_sec(99999999999999.11));
ERROR: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99990100000000.11)', time_to_sec(99990100000000.11));
ERROR: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(20220101010101.11)', time_to_sec(20220101010101.11));
insert into test2 values ('time_to_sec(99991231235959.11)', time_to_sec(99991231235959.11));
insert into test2 values ('time_to_sec(99991231240000.11)', time_to_sec(99991231240000.11));
ERROR: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(10000101000001.11)', time_to_sec(10000101000001.11));
insert into test2 values ('time_to_sec(10000101000059.11)', time_to_sec(10000101000059.11));
insert into test2 values ('time_to_sec(10000101000060.11)', time_to_sec(10000101000060.11));
ERROR: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(0.11)', time_to_sec(0.11));
insert into test2 values ('time_to_sec(100000000000000.11)', time_to_sec(100000000000000.11));
ERROR: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(10000101000001.99999)', time_to_sec(10000101000001.99999));
insert into test2 values('time_to_sec(10000101000001.999999)', time_to_sec(10000101000001.999999));
insert into test2 values('time_to_sec(10000101000001.9999994)', time_to_sec(10000101000001.9999994));
insert into test2 values('time_to_sec(10000101000001.9999995)', time_to_sec(10000101000001.9999995));
insert into test2 values('time_to_sec(10000101000001.999999999)', time_to_sec(10000101000001.999999999));
insert into test2 values('time_to_sec(8385959.99999)', time_to_sec(8385959.99999));
insert into test2 values('time_to_sec(8385959.999999)', time_to_sec(8385959.999999));
insert into test2 values('time_to_sec(8385959.9999994)', time_to_sec(8385959.9999994));
insert into test2 values('time_to_sec(8385959.9999995)', time_to_sec(8385959.9999995));
ERROR: Truncated incorrect time value: "8385959.9999995"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(8385959.999999999)', time_to_sec(8385959.999999999));
ERROR: Truncated incorrect time value: "8385959.999999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.99999)', time_to_sec(-10000101000001.99999));
ERROR: Truncated incorrect time value: "-10000101000001.99999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999999)', time_to_sec(-10000101000001.999999));
ERROR: Truncated incorrect time value: "-10000101000001.999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999994)', time_to_sec(-10000101000001.999994));
ERROR: Truncated incorrect time value: "-10000101000001.999994"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999995)', time_to_sec(-10000101000001.999995));
ERROR: Truncated incorrect time value: "-10000101000001.999995"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999999999)', time_to_sec(-10000101000001.999999999));
ERROR: Truncated incorrect time value: "-10000101000001.999999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-8385959.99999)', time_to_sec(-8385959.99999));
insert into test2 values('time_to_sec(-8385959.999999)', time_to_sec(-8385959.999999));
insert into test2 values('time_to_sec(-8385959.9999994)', time_to_sec(-8385959.9999994));
insert into test2 values('time_to_sec(-8385959.9999995)', time_to_sec(-8385959.9999995));
ERROR: Truncated incorrect time value: "-8385959.9999995"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-8385959.999999999)', time_to_sec(-8385959.999999999));
ERROR: Truncated incorrect time value: "-8385959.999999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001)', time_to_sec(-10000101000001));
ERROR: Truncated incorrect time value: "-10000101000001"
CONTEXT: referenced column: result
select * from test2 order by funcname;
funcname | result
---------------------------------------+----------
time_to_sec('-8385959') | -3020399
time_to_sec('0') | 0
time_to_sec('10000100000000') | 0
time_to_sec('10000100000000') | 0
time_to_sec('10000101000000') | 0
time_to_sec('10000101000001') | 1
time_to_sec('10000101000059') | 59
time_to_sec('20220101010101') | 3661
time_to_sec('8385959') | 3020399
time_to_sec('99990100000000') | 0
time_to_sec('99990100000000') | 0
time_to_sec('99991231235959') | 86399
time_to_sec(-8385959) | -3020399
time_to_sec(-8385959.11) | -3020399
time_to_sec(-8385959.99999) | -3020399
time_to_sec(-8385959.999999) | -3020399
time_to_sec(-8385959.9999994) | -3020399
time_to_sec(0) | 0
time_to_sec(0.11) | 0
time_to_sec(10000101000000) | 0
time_to_sec(10000101000000.11) | 0
time_to_sec(10000101000001) | 1
time_to_sec(10000101000001.11) | 1
time_to_sec(10000101000001.99999) | 1
time_to_sec(10000101000001.999999) | 1
time_to_sec(10000101000001.9999994) | 1
time_to_sec(10000101000001.9999995) | 2
time_to_sec(10000101000001.999999999) | 2
time_to_sec(10000101000059) | 59
time_to_sec(10000101000059.11) | 59
time_to_sec(20220101010101) | 3661
time_to_sec(20220101010101.11) | 3661
time_to_sec(8385959) | 3020399
time_to_sec(8385959.11) | 3020399
time_to_sec(8385959.99999) | 3020399
time_to_sec(8385959.999999) | 3020399
time_to_sec(8385959.9999994) | 3020399
time_to_sec(99991231235959) | 86399
time_to_sec(99991231235959.11) | 86399
(39 rows)
-- 非严格模式 insert
set dolphin.sql_mode = 'sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date';
delete from test2 where 1=1;
insert into test2 values ('time_to_sec(100000000000000)', time_to_sec(100000000000000));
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(10000100000000)', time_to_sec(10000100000000));
WARNING: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99990100000000)', time_to_sec(99990100000000));
WARNING: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''100000000000000'')', time_to_sec('100000000000000'));
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''10000100000000'')', time_to_sec('10000100000000'));
insert into test2 values ('time_to_sec(''99990100000000'')', time_to_sec('99990100000000'));
insert into test2 values ('time_to_sec(''8385960'')', time_to_sec('8385960'));
WARNING: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''8385959'')', time_to_sec('8385959'));
insert into test2 values ('time_to_sec(''-8385960'')', time_to_sec('-8385960'));
WARNING: Truncated incorrect time value: "-8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''-8385959'')', time_to_sec('-8385959'));
insert into test2 values ('time_to_sec(''10000101000000'')', time_to_sec('10000101000000'));
insert into test2 values ('time_to_sec(''10000100000000'')', time_to_sec('10000100000000'));
insert into test2 values ('time_to_sec(''100000101000000'')', time_to_sec('100000101000000'));
WARNING: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''99999999999999'')', time_to_sec('99999999999999'));
WARNING: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''99990100000000'')', time_to_sec('99990100000000'));
insert into test2 values ('time_to_sec(''20220101010101'')', time_to_sec('20220101010101'));
insert into test2 values ('time_to_sec(''99991231235959'')', time_to_sec('99991231235959'));
insert into test2 values ('time_to_sec(''99991231240000'')', time_to_sec('99991231240000'));
WARNING: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''10000101000001'')', time_to_sec('10000101000001'));
insert into test2 values ('time_to_sec(''10000101000059'')', time_to_sec('10000101000059'));
insert into test2 values ('time_to_sec(''10000101000060'')', time_to_sec('10000101000060'));
WARNING: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(''0'')', time_to_sec('0'));
insert into test2 values ('time_to_sec(''100000000000000'')', time_to_sec('100000000000000'));
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385960)', time_to_sec(8385960));
WARNING: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385959)', time_to_sec(8385959));
insert into test2 values ('time_to_sec(-8385960)', time_to_sec(-8385960));
WARNING: Truncated incorrect time value: "-8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(-8385959)', time_to_sec(-8385959));
insert into test2 values ('time_to_sec(10000101000000)', time_to_sec(10000101000000));
insert into test2 values ('time_to_sec(10000100000000)', time_to_sec(10000100000000));
WARNING: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(100000101000000)', time_to_sec(100000101000000));
WARNING: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99999999999999)', time_to_sec(99999999999999));
WARNING: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99990100000000)', time_to_sec(99990100000000));
WARNING: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(20220101010101)', time_to_sec(20220101010101));
insert into test2 values ('time_to_sec(99991231235959)', time_to_sec(99991231235959));
insert into test2 values ('time_to_sec(99991231240000)', time_to_sec(99991231240000));
WARNING: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(10000101000001)', time_to_sec(10000101000001));
insert into test2 values ('time_to_sec(10000101000059)', time_to_sec(10000101000059));
insert into test2 values ('time_to_sec(10000101000060)', time_to_sec(10000101000060));
WARNING: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(0)', time_to_sec(0));
insert into test2 values ('time_to_sec(100000000000000)', time_to_sec(100000000000000));
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385960.11)', time_to_sec(8385960.11));
WARNING: Truncated incorrect time value: "8385960"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(8385959.11)', time_to_sec(8385959.11));
insert into test2 values ('time_to_sec(-8385960.11)', time_to_sec(-8385960.11));
WARNING: Truncated incorrect time value: "-8385960.11"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(-8385959.11)', time_to_sec(-8385959.11));
insert into test2 values ('time_to_sec(10000101000000.11)', time_to_sec(10000101000000.11));
insert into test2 values ('time_to_sec(10000100000000.11)', time_to_sec(10000100000000.11));
WARNING: Truncated incorrect time value: "10000100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(100000101000000.11)', time_to_sec(100000101000000.11));
WARNING: Truncated incorrect time value: "100000101000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99999999999999.11)', time_to_sec(99999999999999.11));
WARNING: Truncated incorrect time value: "99999999999999"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(99990100000000.11)', time_to_sec(99990100000000.11));
WARNING: Truncated incorrect time value: "99990100000000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(20220101010101.11)', time_to_sec(20220101010101.11));
insert into test2 values ('time_to_sec(99991231235959.11)', time_to_sec(99991231235959.11));
insert into test2 values ('time_to_sec(99991231240000.11)', time_to_sec(99991231240000.11));
WARNING: Truncated incorrect time value: "99991231240000"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(10000101000001.11)', time_to_sec(10000101000001.11));
insert into test2 values ('time_to_sec(10000101000059.11)', time_to_sec(10000101000059.11));
insert into test2 values ('time_to_sec(10000101000060.11)', time_to_sec(10000101000060.11));
WARNING: Truncated incorrect time value: "10000101000060"
CONTEXT: referenced column: result
insert into test2 values ('time_to_sec(0.11)', time_to_sec(0.11));
insert into test2 values ('time_to_sec(100000000000000.11)', time_to_sec(100000000000000.11));
WARNING: Truncated incorrect time value: "100000000000000"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(10000101000001.99999)', time_to_sec(10000101000001.99999));
insert into test2 values('time_to_sec(10000101000001.999999)', time_to_sec(10000101000001.999999));
insert into test2 values('time_to_sec(10000101000001.9999994)', time_to_sec(10000101000001.9999994));
insert into test2 values('time_to_sec(10000101000001.9999995)', time_to_sec(10000101000001.9999995));
insert into test2 values('time_to_sec(10000101000001.999999999)', time_to_sec(10000101000001.999999999));
insert into test2 values('time_to_sec(8385959.99999)', time_to_sec(8385959.99999));
insert into test2 values('time_to_sec(8385959.999999)', time_to_sec(8385959.999999));
insert into test2 values('time_to_sec(8385959.9999994)', time_to_sec(8385959.9999994));
insert into test2 values('time_to_sec(8385959.9999995)', time_to_sec(8385959.9999995));
WARNING: Truncated incorrect time value: "8385959.9999995"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(8385959.999999999)', time_to_sec(8385959.999999999));
WARNING: Truncated incorrect time value: "8385959.999999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.99999)', time_to_sec(-10000101000001.99999));
WARNING: Truncated incorrect time value: "-10000101000001.99999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999999)', time_to_sec(-10000101000001.999999));
WARNING: Truncated incorrect time value: "-10000101000001.999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999994)', time_to_sec(-10000101000001.999994));
WARNING: Truncated incorrect time value: "-10000101000001.999994"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999995)', time_to_sec(-10000101000001.999995));
WARNING: Truncated incorrect time value: "-10000101000001.999995"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001.999999999)', time_to_sec(-10000101000001.999999999));
WARNING: Truncated incorrect time value: "-10000101000001.999999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-8385959.99999)', time_to_sec(-8385959.99999));
insert into test2 values('time_to_sec(-8385959.999999)', time_to_sec(-8385959.999999));
insert into test2 values('time_to_sec(-8385959.9999994)', time_to_sec(-8385959.9999994));
insert into test2 values('time_to_sec(-8385959.9999995)', time_to_sec(-8385959.9999995));
WARNING: Truncated incorrect time value: "-8385959.9999995"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-8385959.999999999)', time_to_sec(-8385959.999999999));
WARNING: Truncated incorrect time value: "-8385959.999999999"
CONTEXT: referenced column: result
insert into test2 values('time_to_sec(-10000101000001)', time_to_sec(-10000101000001));
WARNING: Truncated incorrect time value: "-10000101000001"
CONTEXT: referenced column: result
select * from test2 order by funcname;
funcname | result
----------------------------------------+----------
time_to_sec('-8385959') | -3020399
time_to_sec('-8385960') |
time_to_sec('0') | 0
time_to_sec('100000000000000') | 0
time_to_sec('100000000000000') | 0
time_to_sec('100000101000000') | 36000
time_to_sec('10000100000000') | 0
time_to_sec('10000100000000') | 0
time_to_sec('10000101000000') | 0
time_to_sec('10000101000001') | 1
time_to_sec('10000101000059') | 59
time_to_sec('10000101000060') |
time_to_sec('20220101010101') | 3661
time_to_sec('8385959') | 3020399
time_to_sec('8385960') |
time_to_sec('99990100000000') | 0
time_to_sec('99990100000000') | 0
time_to_sec('99991231235959') | 86399
time_to_sec('99991231240000') |
time_to_sec('99999999999999') |
time_to_sec(-10000101000001) |
time_to_sec(-10000101000001.99999) |
time_to_sec(-10000101000001.999994) |
time_to_sec(-10000101000001.999995) |
time_to_sec(-10000101000001.999999) |
time_to_sec(-10000101000001.999999999) |
time_to_sec(-8385959) | -3020399
time_to_sec(-8385959.11) | -3020399
time_to_sec(-8385959.99999) | -3020399
time_to_sec(-8385959.999999) | -3020399
time_to_sec(-8385959.9999994) | -3020399
time_to_sec(-8385959.9999995) | -3020399
time_to_sec(-8385959.999999999) | -3020399
time_to_sec(-8385960) |
time_to_sec(-8385960.11) |
time_to_sec(0) | 0
time_to_sec(0.11) | 0
time_to_sec(100000000000000) |
time_to_sec(100000000000000) |
time_to_sec(100000000000000.11) |
time_to_sec(100000101000000) |
time_to_sec(100000101000000.11) |
time_to_sec(10000100000000) |
time_to_sec(10000100000000) |
time_to_sec(10000100000000.11) |
time_to_sec(10000101000000) | 0
time_to_sec(10000101000000.11) | 0
time_to_sec(10000101000001) | 1
time_to_sec(10000101000001.11) | 1
time_to_sec(10000101000001.99999) | 1
time_to_sec(10000101000001.999999) | 1
time_to_sec(10000101000001.9999994) | 1
time_to_sec(10000101000001.9999995) | 2
time_to_sec(10000101000001.999999999) | 2
time_to_sec(10000101000059) | 59
time_to_sec(10000101000059.11) | 59
time_to_sec(10000101000060) |
time_to_sec(10000101000060.11) |
time_to_sec(20220101010101) | 3661
time_to_sec(20220101010101.11) | 3661
time_to_sec(8385959) | 3020399
time_to_sec(8385959.11) | 3020399
time_to_sec(8385959.99999) | 3020399
time_to_sec(8385959.999999) | 3020399
time_to_sec(8385959.9999994) | 3020399
time_to_sec(8385959.9999995) | 3020399
time_to_sec(8385959.999999999) | 3020399
time_to_sec(8385960) |
time_to_sec(8385960.11) |
time_to_sec(99990100000000) |
time_to_sec(99990100000000) |
time_to_sec(99990100000000.11) |
time_to_sec(99991231235959) | 86399
time_to_sec(99991231235959.11) | 86399
time_to_sec(99991231240000) |
time_to_sec(99991231240000.11) |
time_to_sec(99999999999999) |
time_to_sec(99999999999999.11) |
(78 rows)
drop table test2;
\c contrib_regression
set dolphin.b_compatibility_mode = off;
DROP DATABASE b_datetime_func_test3;