func_time.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

# unix_timestamp() should return error for too big or negative argument.
# It should return Epoch value for zero argument since it seems that many
# users rely on this fact, from_unixtime() should work with values
# up to INT_MAX32 because of the same reason.
#
select from_unixtime(-1);
# check for from_unixtime(2^31-1) and from_unixtime(2^31)
select from_unixtime(2147483647);
select from_unixtime(2147483648);
select from_unixtime(0);

#
# Some more tests for bug #9191 "TIMESTAMP/from_unixtime() no
# longer accepts 2^31-1". Here we test that from_unixtime and
# unix_timestamp are consistent, when working with boundary dates.
#
select unix_timestamp(from_unixtime(2147483647));
select unix_timestamp(from_unixtime(2147483648));

# check for invalid dates

# bad year
select unix_timestamp('2039-01-20 01:00:00');
select unix_timestamp('1968-01-20 01:00:00');
# bad month
select unix_timestamp('2038-02-10 01:00:00');
select unix_timestamp('1969-11-20 01:00:00');
# bad day
select unix_timestamp('2038-01-20 01:00:00');
select unix_timestamp('1969-12-30 01:00:00');

#
# Check negative shift (we subtract several days for boundary dates during
# conversion).
select unix_timestamp('2038-01-17 12:00:00');

#
# Check positive shift. (it happens only on
# platfroms with unsigned time_t, such as QNX)
#
select unix_timestamp('1970-01-01 03:00:01');

# check bad date, close to the boundary (we cut them off in the very end)
select unix_timestamp('2038-01-19 07:14:07');


#
# Test types from + INTERVAL
#

CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);
INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08");
SELECT * from t1;
select date_add("1997-12-31",INTERVAL 1 SECOND);
select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);

select date_add(datetime, INTERVAL 1 SECOND) from t1;
select date_add(datetime, INTERVAL 1 YEAR) from t1;

select date_add(date,INTERVAL 1 SECOND) from t1;
select date_add(date,INTERVAL 1 MINUTE) from t1;
select date_add(date,INTERVAL 1 HOUR) from t1;
select date_add(date,INTERVAL 1 DAY) from t1;
select date_add(date,INTERVAL 1 MONTH) from t1;
select date_add(date,INTERVAL 1 YEAR) from t1;
select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;
select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;
select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;
select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;
select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;
select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;
select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
select date_add(date,INTERVAL "1" WEEK) from t1;
select date_add(date,INTERVAL "1" QUARTER) from t1;
select timestampadd(MINUTE, 1, date) from t1;
select timestampadd(WEEK, 1, date) from t1;
select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
# Prepared statements doesn't support FRAC_SECOND yet
--disable_ps_protocol
select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;
--enable_ps_protocol

select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;

select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,
       timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,
       timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
       timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;

# bug 16226
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');

# end of bug

select date_add(time,INTERVAL 1 SECOND) from t1;
drop table t1;

# test for last_day
select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,
       last_day('2003-03-32') as f3, last_day('2003-04-01') as f4,
       last_day('2001-01-01 01:01:01') as f5, last_day(NULL),
       last_day('2001-02-12');

create table t1 select last_day('2000-02-05') as a,
                from_days(to_days("960101")) as b;
describe t1;
select * from t1;
drop table t1;
select last_day('2000-02-05') as a,
       from_days(to_days("960101")) as b;

select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
select length(last_day("1997-12-1"));
select last_day("1997-12-1")+0;
select last_day("1997-12-1")+0.0;

# Test SAPDB UTC_% functions. This part is TZ dependant (It is supposed that
# TZ variable set to GMT-3

select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;
select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;
select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;
select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;

explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);

SET @TMP='2007-08-01 12:22:49';
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES ('2007-08-01 12:22:59');
INSERT INTO t1 VALUES ('2007-08-01 12:23:01');
INSERT INTO t1 VALUES ('2007-08-01 12:23:20');
SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
DROP TABLE t1;

#
# Bug #10568
#

select last_day('2005-00-00');
select last_day('2005-00-01');
select last_day('2005-01-00');

#
# Bug #18501: monthname and NULLs
#

select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
       monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));

#
# Bug #16327: problem with timestamp < 1970
#

set time_zone='-6:00';
create table t1(a timestamp);
insert into t1 values (19691231190001);
select * from t1;
drop table t1;

#
# Bug#16377 result of DATE/TIME functions were compared as strings which
#           can lead to a wrong result.
# Now wrong dates should be compared only with CAST()
create table t1(f1 date, f2 time, f3 datetime);
insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
drop table t1;

#
# Bug #16546
# 

create table t1 select now() - now(), curtime() - curtime(), 
                       sec_to_time(1) + 0, from_unixtime(1) + 0;
show create table t1;
drop table t1;

#
# Bug #11655: Wrong time is returning from nested selects - maximum time exists
#
# check if SEC_TO_TIME() handles out-of-range values correctly
SELECT SEC_TO_TIME(3300000);
SELECT SEC_TO_TIME(3300000)+0;
SELECT SEC_TO_TIME(3600 * 4294967296);

# check if TIME_TO_SEC() handles out-of-range values correctly
SELECT TIME_TO_SEC('916:40:00');

# check if ADDTIME() handles out-of-range values correctly
SELECT ADDTIME('500:00:00', '416:40:00');
SELECT ADDTIME('916:40:00', '416:40:00');

# check if SUBTIME() handles out-of-range values correctly
SELECT SUBTIME('916:40:00', '416:40:00');
SELECT SUBTIME('-916:40:00', '416:40:00');

# check if MAKETIME() handles out-of-range values correctly
SELECT MAKETIME(916,0,0);
SELECT MAKETIME(4294967296, 0, 0);
SELECT MAKETIME(-4294967296, 0, 0);
SELECT MAKETIME(0, 4294967296, 0);
SELECT MAKETIME(0, 0, 4294967296);
SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0);

# check if EXTRACT() handles out-of-range values correctly
SELECT EXTRACT(HOUR FROM '100000:02:03');

# check if we get proper warnings if both input string truncation
# and out-of-range value occur
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES('916:00:00 a');