func_time.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 1,265 行 · 第 1/4 页

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 MAKETIME(CAST(-1 AS UNSIGNED), 0, 0);
MAKETIME(CAST(-1 AS UNSIGNED), 0, 0)
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '18446744073709551615:00:00'
SELECT EXTRACT(HOUR FROM '100000:02:03');
EXTRACT(HOUR FROM '100000:02:03')
838
Warnings:
Warning	1292	Truncated incorrect time value: '100000:02:03'
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES('916:00:00 a');
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
Warning	1264	Out of range value adjusted for column 'f1' at row 1
SELECT * FROM t1;
f1
838:59:59
DROP TABLE t1;
SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED));
SEC_TO_TIME(CAST(-1 AS UNSIGNED))
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '18446744073709551615'
SET NAMES latin1;
SET character_set_results = NULL;
SHOW VARIABLES LIKE 'character_set_results';
Variable_name	Value
character_set_results	
CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY);
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;
SET NAMES DEFAULT;
(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
select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
week(20061108), week(20061108.01), week(20061108085411.000002);
isnull(week(now() + 0))	isnull(week(now() + 0.2))	week(20061108)	week(20061108.01)	week(20061108085411.000002)
0	0	45	45	45
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	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,_latin1'2001-02-01 12:59:59.120000',_latin1'2001-05-01 12:58:58.119999') AS `a2`
select last_day('2005-00-00');
last_day('2005-00-00')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '2005-00-00'
select last_day('2005-00-01');
last_day('2005-00-01')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '2005-00-01'
select last_day('2005-01-00');
last_day('2005-01-00')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '2005-01-00'
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
create table t1 (a timestamp default '2005-05-05 01:01:01',
b timestamp default '2005-05-05 01:01:01');
create function t_slow_sysdate() returns timestamp
begin
do sleep(2);
return sysdate();
end;
//
insert into t1 set a = sysdate(), b = t_slow_sysdate();//
create trigger t_before before insert on t1
for each row begin
set new.b = t_slow_sysdate();
end
//
insert into t1 set a = sysdate();
select a != b from t1;
a != b
1
1
drop trigger t_before;
drop function t_slow_sysdate;
drop table t1;
create table t1 (a datetime, i int, b datetime);
insert into t1 select sysdate(), sleep(1), sysdate() from dual;
select a != b from t1;
a != b
1
drop table t1;
create procedure t_sysdate()
begin
select sysdate() into @a;
do sleep(2);
select sysdate() into @b;
select @a != @b;
end;
//
call t_sysdate();
@a != @b
1
drop procedure t_sysdate;
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;
set time_zone= @@global.time_zone;
SET NAMES latin1;
SET character_set_results = NULL;
SHOW VARIABLES LIKE 'character_set_results';
Variable_name	Value
character_set_results	
CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY);
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;
SET NAMES DEFAULT;
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	1
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
set names latin1;
create table t1 (a varchar(15) character set ascii not null);
insert into t1 values ('070514-000000');
select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull'))
#
set names swe7;
select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (swe7_swedish_ci,COERCIBLE) for operation 'concat'
set names latin1;
set lc_time_names=fr_FR;
select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'concat'
set lc_time_names=en_US;
drop table t1;
End of 5.0 tests