~drizzle-trunk/drizzle/development

drop table if exists t1,t2,t3;
select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29");
from_days(to_days("960101"))	to_days(960201)-to_days("19960101")	to_days(date_add(curdate(), interval 1 day))-to_days(curdate())	weekday("1997-11-29")
1996-01-01	31	1	5
select period_add("9602",-12),period_diff(199505,"9404") ;
period_add("9602",-12)	period_diff(199505,"9404")
199502	13
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(now())
0.000000	0	0
select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
from_unixtime(unix_timestamp("1994-03-02 10:11:12"))	from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s")	from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112.000000
select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
sec_to_time(time_to_sec("0:30:47")/6.21);
sec_to_time(9001)	sec_to_time(9001)+0	time_to_sec("15:12:22")	sec_to_time(time_to_sec("0:30:47")/6.21)
02:30:01	23001.000000	54742	00:04:57
select sec_to_time(time_to_sec('-838:59:59'));
sec_to_time(time_to_sec('-838:59:59'))
-838:59:59
select now()-curdate()*1000000-curtime();
now()-curdate()*1000000-curtime()
0.000000
select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
strcmp(current_timestamp(),concat(current_date()," ",current_time()))
0
select strcmp(localtime(),concat(current_date()," ",current_time()));
strcmp(localtime(),concat(current_date()," ",current_time()))
0
select strcmp(localtimestamp(),concat(current_date()," ",current_time()));
strcmp(localtimestamp(),concat(current_date()," ",current_time()))
0
select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");
date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")
January Thursday 2nd 1997 97 01 02 03 04 05 4
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"))
January Thursday 2nd 1997 97 01 02 12 00 00 4
select dayofmonth("1997-01-02"),dayofmonth(19970323);
dayofmonth("1997-01-02")	dayofmonth(19970323)
2	23
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
month("1997-01-02")	year("98-02-03")	dayofyear("1997-12-31")
1	1998	365
select month("2001-02-00"),year("2001-01-01");
ERROR HY000: Received an invalid datetime value '2001-02-00'.
select DAYOFYEAR("1997-03-03"), QUARTER(980303);
DAYOFYEAR("1997-03-03")	QUARTER(980303)
62	1
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)
23	3	22
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')
1998-53	1998-53
select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');
date_format('1999-12-31','%x-%v')	date_format('2000-01-01','%x-%v')
1999-52	1999-52
select dayname("1962-03-03"),dayname("1962-03-03")+0;
dayname("1962-03-03")	dayname("1962-03-03")+0
Saturday	5
select monthname("1972-03-04"),monthname("1972-03-04")+0;
monthname("1972-03-04")	monthname("1972-03-04")+0
March	3
select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
00|12|0|12|00|AM|12:00:00 AM|00|00:00:00
select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
01|01|1|1|02|AM|01:02:03 AM|03|01:02:03
select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15
select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
01|01|1|1|00|AM|01:00:15 AM|15|01:00:15
select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w')
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6
select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w')
NULL
select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)
1998-01-01 00:00:00
select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE)
1998-01-01 00:00:59
select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);
date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR)
1998-01-01 00:59:59
select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);
date_add("1997-12-31 23:59:59",INTERVAL 1 DAY)
1998-01-01 23:59:59
select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);
date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH)
1998-01-31 23:59:59
select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);
date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR)
1998-12-31 23:59:59
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)
1998-01-01 00:01:00
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)
1998-01-01 01:00:59
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);
date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR)
1998-01-02 00:59:59
select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);
date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)
1999-01-31 23:59:59
select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)
1998-01-01 01:01:00
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE)
1998-01-02 01:00:59
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND)
1998-01-02 01:01:00
select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)
1997-12-31 23:59:59
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);
date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)
1997-12-31 23:59:00
select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);
date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR)
1997-12-31 23:00:00
select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);
date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)
1997-12-31 00:00:00
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);
date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)
1997-12-01 00:00:00
select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);
date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR)
1997-01-01 00:00:00
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND)
1997-12-31 23:58:59
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE)
1997-12-31 22:59:00
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR)
1997-12-30 23:00:00
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);
date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH)
1996-12-01 00:00:00
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND)
1997-12-31 22:58:59
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE)
1997-12-30 22:59:00
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND)
1997-12-30 22:58:59
select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);
date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND)
1998-01-02 03:46:39
select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE)
1997-10-23 13:19:59
select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);
date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR)
2009-05-29 15:59:59
select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);
date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY)
1724-03-17 23:59:59
select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);
date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);
date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND)
1998-01-07 22:40:00
select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE)
1996-11-10 07:58:59
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR)
2025-05-19 00:59:59
select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);
date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH)
1897-11-30 23:59:59
select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND)
1999-02-21 17:40:38
select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE)
1970-08-11 19:20:59
select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND)
2025-05-23 04:40:38
select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
"1997-12-31 23:59:59" + INTERVAL 1 SECOND
1998-01-01 00:00:00
select INTERVAL 1 DAY + "1997-12-31";
INTERVAL 1 DAY + "1997-12-31"
1998-01-01
select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;
"1998-01-01 00:00:00" - INTERVAL 1 SECOND
1997-12-31 23:59:59
select date_sub("1998-01-02",INTERVAL 31 DAY);
date_sub("1998-01-02",INTERVAL 31 DAY)
1997-12-02
select date_add("1997-12-31",INTERVAL 1 SECOND);
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01
select date_add("1997-12-31",INTERVAL 1 DAY);
date_add("1997-12-31",INTERVAL 1 DAY)
1998-01-01
select date_add(NULL,INTERVAL 100000 SECOND);
date_add(NULL,INTERVAL 100000 SECOND)
NULL
select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);
date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND)
NULL
select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND)
NULL
select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);
date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);
date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND)
NULL
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
select date_add('1998-01-30',Interval 1 month);
date_add('1998-01-30',Interval 1 month)
1998-02-28
select date_add('1998-01-30',Interval '2:1' year_month);
date_add('1998-01-30',Interval '2:1' year_month)
2000-02-29
select date_add('1996-02-29',Interval '1' year);
date_add('1996-02-29',Interval '1' year)
1997-02-28
select extract(YEAR FROM "1999-01-02 10:11:12");
extract(YEAR FROM "1999-01-02 10:11:12")
1999
select extract(YEAR_MONTH FROM "1999-01-02");
extract(YEAR_MONTH FROM "1999-01-02")
199901
select extract(DAY FROM "1999-01-02");
extract(DAY FROM "1999-01-02")
2
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
extract(DAY_HOUR FROM "1999-01-02 10:11:12")
210
select extract(DAY_MINUTE FROM "02 10:11:12");
ERROR HY000: Received an invalid datetime value '02 10:11:12'.
select extract(DAY_SECOND FROM "225 10:11:12");
ERROR HY000: Received an invalid datetime value '225 10:11:12'.
select extract(HOUR FROM "1999-01-02 10:11:12");
extract(HOUR FROM "1999-01-02 10:11:12")
10
select extract(HOUR_MINUTE FROM "10:11:12");
extract(HOUR_MINUTE FROM "10:11:12")
1011
select extract(HOUR_SECOND FROM "10:11:12");
extract(HOUR_SECOND FROM "10:11:12")
101112
select extract(MINUTE FROM "10:11:12");
extract(MINUTE FROM "10:11:12")
11
select extract(MINUTE_SECOND FROM "10:11:12");
extract(MINUTE_SECOND FROM "10:11:12")
1112
select extract(SECOND FROM "1999-01-02 10:11:12");
extract(SECOND FROM "1999-01-02 10:11:12")
12
select extract(MONTH FROM "2001-02-00");
ERROR HY000: Received an invalid datetime value '2001-02-00'.
SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;
quarter
4
SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;
quarter
4
SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;
quarter
4
SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)
9999-12-31 00:00:00
SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)
9999-12-31 00:00:00
SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;
"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND
1968-01-20 03:14:08
SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND
1968-01-20 03:15:07
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND
8895-03-27 22:11:40
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
create table t1 (ctime varchar(20));
insert into t1 values ('2001-01-12 12:23:40');
select ctime, hour(ctime) from t1;
ctime	hour(ctime)
2001-01-12 12:23:40	12
select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
ctime
2001-01-12 12:23:40
drop table t1;
create table t1 (id int);
create table t2 (id int, date date);
insert into t1 values (1);
insert into t2 values (1, NULL);
insert into t1 values (2);
insert into t2 values (2, "2000-01-01");
select monthname(date) from t1 inner join t2 on t1.id = t2.id;
monthname(date)
NULL
NULL
select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;
monthname(date)
NULL
NULL
drop table t1,t2;
CREATE TABLE t1 (updated text) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('');
SELECT month(updated) from t1;
ERROR HY000: Received an invalid datetime value ''.
SELECT year(updated) from t1;
ERROR HY000: Received an invalid datetime value ''.
drop table t1;
create table t1 (d date, dt datetime, t timestamp, c char(10));
insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1;
to_days("0000-00-00")	to_days(d)	to_days(dt)	to_days(t)	to_days(c)
NULL	NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00'
Warning	1292	Incorrect datetime value: '0000-00-00'
select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
drop table t1;
CREATE TABLE t1 ( start datetime default NULL);
INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');
CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL);
INSERT INTO t2 VALUES (20021029165106,20021105164731);
CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);
INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");
select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
drop table t1,t2,t3;
select @a:=FROM_UNIXTIME(1);
@a:=FROM_UNIXTIME(1)
1970-01-01 03:00:01
select unix_timestamp(@a);
unix_timestamp(@a)
1
select unix_timestamp('1969-12-01 19:00:01');
unix_timestamp('1969-12-01 19:00:01')
0
select from_unixtime(-1);
from_unixtime(-1)
NULL
select from_unixtime(2147483647);
from_unixtime(2147483647)
2038-01-19 06:14:07
select from_unixtime(2147483648);
from_unixtime(2147483648)
NULL
select from_unixtime(0);
from_unixtime(0)
1970-01-01 03:00:00
select unix_timestamp(from_unixtime(2147483647));
unix_timestamp(from_unixtime(2147483647))
2147483647
select unix_timestamp(from_unixtime(2147483648));
unix_timestamp(from_unixtime(2147483648))
NULL
select unix_timestamp('2039-01-20 01:00:00');
unix_timestamp('2039-01-20 01:00:00')
0
select unix_timestamp('1968-01-20 01:00:00');
unix_timestamp('1968-01-20 01:00:00')
0
select unix_timestamp('2038-02-10 01:00:00');
unix_timestamp('2038-02-10 01:00:00')
0
select unix_timestamp('1969-11-20 01:00:00');
unix_timestamp('1969-11-20 01:00:00')
0
select unix_timestamp('2038-01-20 01:00:00');
unix_timestamp('2038-01-20 01:00:00')
0
select unix_timestamp('1969-12-30 01:00:00');
unix_timestamp('1969-12-30 01:00:00')
0
select unix_timestamp('2038-01-17 12:00:00');
unix_timestamp('2038-01-17 12:00:00')
2147331600
select unix_timestamp('1970-01-01 03:00:01');
unix_timestamp('1970-01-01 03:00:01')
1
select unix_timestamp('2038-01-19 07:14:07');
unix_timestamp('2038-01-19 07:14:07')
0
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;
datetime	timestamp	date	time
2001-01-02 03:04:05	2002-01-02 03:04:05	2003-01-02	06:07:08
select date_add("1997-12-31",INTERVAL 1 SECOND);
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01
select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);
date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH)
1999-01-31
select date_add(datetime, INTERVAL 1 SECOND) from t1;
date_add(datetime, INTERVAL 1 SECOND)
2001-01-02 03:04:06
select date_add(datetime, INTERVAL 1 YEAR) from t1;
date_add(datetime, INTERVAL 1 YEAR)
2002-01-02 03:04:05
select date_add(date,INTERVAL 1 SECOND) from t1;
date_add(date,INTERVAL 1 SECOND)
2003-01-02 00:00:01
select date_add(date,INTERVAL 1 MINUTE) from t1;
date_add(date,INTERVAL 1 MINUTE)
2003-01-02 00:01:00
select date_add(date,INTERVAL 1 HOUR) from t1;
date_add(date,INTERVAL 1 HOUR)
2003-01-02 01:00:00
select date_add(date,INTERVAL 1 DAY) from t1;
date_add(date,INTERVAL 1 DAY)
2003-01-03
select date_add(date,INTERVAL 1 MONTH) from t1;
date_add(date,INTERVAL 1 MONTH)
2003-02-02
select date_add(date,INTERVAL 1 YEAR) from t1;
date_add(date,INTERVAL 1 YEAR)
2004-01-02
select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;
date_add(date,INTERVAL "1:1" MINUTE_SECOND)
2003-01-02 00:01:01
select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;
date_add(date,INTERVAL "1:1" HOUR_MINUTE)
2003-01-02 01:01:00
select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;
date_add(date,INTERVAL "1:1" DAY_HOUR)
2003-01-03 01:00:00
select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;
date_add(date,INTERVAL "1 1" YEAR_MONTH)
2004-02-02
select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;
date_add(date,INTERVAL "1:1:1" HOUR_SECOND)
2003-01-02 01:01:01
select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;
date_add(date,INTERVAL "1 1:1" DAY_MINUTE)
2003-01-03 01:01:00
select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
date_add(date,INTERVAL "1 1:1:1" DAY_SECOND)
2003-01-03 01:01:01
select date_add(date,INTERVAL "1" WEEK) from t1;
date_add(date,INTERVAL "1" WEEK)
2003-01-09
select date_add(date,INTERVAL "1" QUARTER) from t1;
date_add(date,INTERVAL "1" QUARTER)
2003-04-02
select timestampadd(MINUTE, 1, date) from t1;
timestampadd(MINUTE, 1, date)
2003-01-02 00:01:00
select timestampadd(WEEK, 1, date) from t1;
timestampadd(WEEK, 1, date)
2003-01-09
select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
timestampadd(SQL_TSI_SECOND, 1, date)
2003-01-02 00:00:01
select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;
timestampadd(SQL_TSI_FRAC_SECOND, 1, date)
2003-01-02 00:00:00.000001
select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
a
3
select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
a
-1
select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
a
-5
select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
a
0
select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
a
107
select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
a
12
select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
a
2136
select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
a
89
select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
a
128159
select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
a
7689539
select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;
a
7689538999999
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;
a1	a2	a3	a4
28	28	29	29
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27')
0
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28')
2
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29')
2
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27')
0
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28')
2
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29')
2
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27')
0
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28')
2
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29')
2
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27')
0
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28')
2
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29')
2
select date_add(time,INTERVAL 1 SECOND) from t1;
date_add(time,INTERVAL 1 SECOND)
NULL
Warnings:
Warning	1264	Out of range value for column 'time' at row 1
drop table t1;
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');
f1	f2	f3	f4	f5	last_day(NULL)	last_day('2001-02-12')
2000-02-29	2002-12-31	NULL	2003-04-30	2001-01-31	NULL	2001-02-28
Warnings:
Warning	1292	Incorrect datetime value: '2003-03-32'
create table t1 select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
describe t1;
Field	Type	Null	Key	Default	Extra
a	date	NO		NULL	
b	date	YES		NULL	
select * from t1;
a	b
2000-02-29	1996-01-01
drop table t1;
select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
a	b
2000-02-29	1996-01-01
select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
date_add(last_day("1997-12-1"), INTERVAL 1 DAY)
1998-01-01
select length(last_day("1997-12-1"));
length(last_day("1997-12-1"))
10
select last_day("1997-12-1")+0;
last_day("1997-12-1")+0
19971231
select last_day("1997-12-1")+0.0;
last_day("1997-12-1")+0.0
19971231.0
select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;
strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0
1
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;
strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0
1
select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;
strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0
1
select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;
strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0
1
select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;
strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0
1
select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;
strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0
1
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),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);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `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')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `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);
count(*)
3
DROP TABLE t1;
select last_day('2005-00-00');
last_day('2005-00-00')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2005-00-00'
select last_day('2005-00-01');
last_day('2005-00-01')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2005-00-01'
select last_day('2005-01-00');
last_day('2005-01-00')
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2005-01-00'
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'));
ERROR HY000: Received an invalid datetime value '0000-01-00'.
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);
f1
2006-01-01
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
f1
2006-01-01
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
f1
2006-01-01
select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
f2
12:01:02
select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
f2
12:01:02
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);
f3
2006-01-01 12:01:01
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);
f3
2006-01-01 12:01:01
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
f1
2006-01-01
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
f1
2006-01-01
select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
f1
Warnings:
Warning	1292	Incorrect datetime value: 'zzz'
Warning	1292	Incorrect datetime value: 'zzz'
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
f1
2006-01-01
select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
f1
2006-01-02
drop table t1;
create table t1 select now() - now(), curtime() - curtime(), 
sec_to_time(1) + 0, from_unixtime(1) + 0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `now() - now()` double(23,6),
  `curtime() - curtime()` double(23,6),
  `sec_to_time(1) + 0` double(23,6),
  `from_unixtime(1) + 0` double(23,6)
) ENGINE=InnoDB
drop table t1;
SELECT SEC_TO_TIME(3300000);
SEC_TO_TIME(3300000)
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '3300000'
SELECT SEC_TO_TIME(3300000)+0;
SEC_TO_TIME(3300000)+0
8385959.000000
Warnings:
Warning	1292	Truncated incorrect time value: '3300000'
SELECT SEC_TO_TIME(3600 * 4294967296);
SEC_TO_TIME(3600 * 4294967296)
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '15461882265600'
SELECT TIME_TO_SEC('916:40:00');
TIME_TO_SEC('916:40:00')
3020399
Warnings:
Warning	1292	Truncated incorrect time value: '916:40:00'
SELECT ADDTIME('500:00:00', '416:40:00');
ADDTIME('500:00:00', '416:40:00')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '916:40:00'
SELECT ADDTIME('916:40:00', '416:40:00');
ADDTIME('916:40:00', '416:40:00')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '916:40:00'
Warning	1292	Truncated incorrect time value: '1255:39:59'
SELECT SUBTIME('916:40:00', '416:40:00');
SUBTIME('916:40:00', '416:40:00')
422:19:59
Warnings:
Warning	1292	Truncated incorrect time value: '916:40:00'
SELECT SUBTIME('-916:40:00', '416:40:00');
SUBTIME('-916:40:00', '416:40:00')
-838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '-916:40:00'
Warning	1292	Truncated incorrect time value: '-1255:39:59'
SELECT MAKETIME(916,0,0);
MAKETIME(916,0,0)
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '916:00:00'
SELECT MAKETIME(4294967296, 0, 0);
MAKETIME(4294967296, 0, 0)
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '4294967296:00:00'
SELECT MAKETIME(-4294967296, 0, 0);
MAKETIME(-4294967296, 0, 0)
-838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '-4294967296:00:00'
SELECT MAKETIME(0, 4294967296, 0);
MAKETIME(0, 4294967296, 0)
NULL
SELECT MAKETIME(0, 0, 4294967296);
MAKETIME(0, 0, 4294967296)
NULL
SELECT EXTRACT(HOUR FROM '100000:02:03');
ERROR HY000: Received an invalid datetime value '100000:02:03'.
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES('916:00:00 a');
ERROR 22007: Incorrect time value: '916:00:00 a' for column 'f1' at row 1
SELECT * FROM t1;
f1
DROP TABLE t1;
SHOW VARIABLES LIKE 'character_set_results';
Variable_name	Value
CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32));
INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');
SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;
fmtddate	field2
Sep-4 12:00AM	abcd
DROP TABLE testBug8868;
CREATE TABLE t1 (
a TIMESTAMP
);
INSERT INTO t1 VALUES (now()), (now());
SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a);
1
1
1
DROP TABLE t1;
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H);
H
120
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H);
H
120
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H);
H
05
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H);
H
5
select last_day('0000-00-00');
last_day('0000-00-00')
NULL
End of 4.1 tests
explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select timestampdiff(WEEK,'2001-02-01','2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,'2001-02-01 12:59:59.120000','2001-05-01 12:58:58.119999') AS `a2`
select time_format('100:00:00', '%H %k %h %I %l');
time_format('100:00:00', '%H %k %h %I %l')
100 100 04 04 4
select timestampdiff(month,'2004-09-11','2004-09-11');
timestampdiff(month,'2004-09-11','2004-09-11')
0
select timestampdiff(month,'2004-09-11','2005-09-11');
timestampdiff(month,'2004-09-11','2005-09-11')
12
select timestampdiff(month,'2004-09-11','2006-09-11');
timestampdiff(month,'2004-09-11','2006-09-11')
24
select timestampdiff(month,'2004-09-11','2007-09-11');
timestampdiff(month,'2004-09-11','2007-09-11')
36
select timestampdiff(month,'2005-09-11','2004-09-11');
timestampdiff(month,'2005-09-11','2004-09-11')
-12
select timestampdiff(month,'2005-09-11','2003-09-11');
timestampdiff(month,'2005-09-11','2003-09-11')
-24
select timestampdiff(month,'2004-02-28','2005-02-28');
timestampdiff(month,'2004-02-28','2005-02-28')
12
select timestampdiff(month,'2004-02-29','2005-02-28');
timestampdiff(month,'2004-02-29','2005-02-28')
11
select timestampdiff(month,'2004-02-28','2005-02-28');
timestampdiff(month,'2004-02-28','2005-02-28')
12
select timestampdiff(month,'2004-03-29','2005-03-28');
timestampdiff(month,'2004-03-29','2005-03-28')
11
select timestampdiff(month,'2003-02-28','2004-02-29');
timestampdiff(month,'2003-02-28','2004-02-29')
12
select timestampdiff(month,'2003-02-28','2005-02-28');
timestampdiff(month,'2003-02-28','2005-02-28')
24
select timestampdiff(month,'1999-09-11','2001-10-10');
timestampdiff(month,'1999-09-11','2001-10-10')
24
select timestampdiff(month,'1999-09-11','2001-9-11');
timestampdiff(month,'1999-09-11','2001-9-11')
24
select timestampdiff(year,'1999-09-11','2001-9-11');
timestampdiff(year,'1999-09-11','2001-9-11')
2
select timestampdiff(year,'2004-02-28','2005-02-28');
timestampdiff(year,'2004-02-28','2005-02-28')
1
select timestampdiff(year,'2004-02-29','2005-02-28');
timestampdiff(year,'2004-02-29','2005-02-28')
0
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date);
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date);
INSERT INTO t1 VALUES
(1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01');
INSERT INTO t2 VALUES
(1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');
SELECT * FROM t1, t2 
WHERE t1.day BETWEEN 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
id	day	id	day
1	2005-06-01	1	2005-08-01
3	2005-07-01	1	2005-08-01
1	2005-06-01	2	2005-06-15
1	2005-06-01	3	2005-07-15
3	2005-07-01	3	2005-07-15
SELECT * FROM t1, t2 
WHERE CAST(t1.day AS DATE) BETWEEN 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
id	day	id	day
1	2005-06-01	1	2005-08-01
3	2005-07-01	1	2005-08-01
1	2005-06-01	2	2005-06-15
1	2005-06-01	3	2005-07-15
3	2005-07-01	3	2005-07-15
DROP TABLE t1,t2;
select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
NULL
create table t1 (field DATE);
insert into t1 values ('2006-11-06');
select * from t1 where field < '2006-11-06 04:08:36.0';
field
2006-11-06
select * from t1 where field = '2006-11-06 04:08:36.0';
field
select * from t1 where field = '2006-11-06';
field
2006-11-06
select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
field
2006-11-06
select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
field
2006-11-06
drop table t1;
CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY  (a));
INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), 
(2, '11:00:00', '11:15:00', '1972-02-06');
SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) 
FROM t1;
t1	t2	SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) )	QUARTER(d)
10:00:00	NULL	NULL	NULL
11:00:00	11:15:00	00:15:00	NULL
SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
FROM t1 ORDER BY a DESC;
t1	t2	SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) )	QUARTER(d)
11:00:00	11:15:00	00:15:00	1
10:00:00	NULL	NULL	NULL
DROP TABLE t1;
SELECT TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") FROM (SELECT 3020399 AS a UNION SELECT 3020398 ) x GROUP BY 1;
TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s")
838:59:58
838:59:59
select DATE_ADD('20071108181000', INTERVAL 1 DAY);
DATE_ADD('20071108181000', INTERVAL 1 DAY)
2007-11-09 18:10:00
select DATE_ADD(20071108181000,   INTERVAL 1 DAY);
DATE_ADD(20071108181000,   INTERVAL 1 DAY)
2007-11-09 18:10:00
select DATE_ADD('20071108',       INTERVAL 1 DAY);
DATE_ADD('20071108',       INTERVAL 1 DAY)
2007-11-09
select DATE_ADD(20071108,         INTERVAL 1 DAY);
DATE_ADD(20071108,         INTERVAL 1 DAY)
2007-11-09
select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND;
LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND
2007-12-30 23:59:59
SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18');
TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18')
2008-02-18 00:00:00.000001
SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18');
TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18')
86400000000
SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND)' at line 1
SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND)' at line 1
SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND' at line 1
SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND' at line 1
End of 5.0 tests
select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND)
0049-12-31 23:59:59
select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND)
0198-12-31 23:59:59
select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);
date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND)
0200-01-01 00:00:01
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
0199-12-31 23:59:59
select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND)
0200-01-01 00:00:00
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
0199-12-31 23:59:59
select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR)
0001-01-01 23:59:59
select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND)
2049-12-31 23:59:59
select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND)
1989-12-31 23:59:59
select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND)
0068-12-31 23:59:59
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)
0168-12-31 23:59:59
End of 5.1 tests