func_time.test

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

SELECT * FROM t1;
DROP TABLE t1;

#
# Bug #20927: sec_to_time treats big unsigned as signed
#
# check if SEC_TO_TIME() handles BIGINT UNSIGNED values correctly
SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED));
# 21913: DATE_FORMAT() Crashes mysql server if I use it through
#        mysql-connector-j driver.
#

SET NAMES latin1;
SET character_set_results = NULL;
SHOW VARIABLES LIKE '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;

DROP TABLE testBug8868;

SET NAMES DEFAULT;


#
# Bug #19844 time_format in Union truncates values
#

(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);
(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);
(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);

(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);

#
# Bug #23653: crash if last_day('0000-00-00')
#

select last_day('0000-00-00');

#
# Bug 23616: datetime functions with double argumets
#

select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
  week(20061108), week(20061108.01), week(20061108085411.000002);

--echo 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;

#
# Bug #10568
#

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

#
# Bug #10590: %h, %I, and %l format specifies should all return results in
# the 0-11 range
#
select time_format('100:00:00', '%H %k %h %I %l');

#
# Bug #12562: Make SYSDATE behave like it does in Oracle: always the current
#             time, regardless of magic to make NOW() always the same for the
#             entirety of a statement.
create table t1 (a timestamp default '2005-05-05 01:01:01',
                 b timestamp default '2005-05-05 01:01:01');
delimiter //;
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
//

delimiter ;//

insert into t1 set a = sysdate();

select a != b from t1;

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;
drop table t1;

delimiter //;
create procedure t_sysdate()
begin
  select sysdate() into @a;
  do sleep(2);
  select sysdate() into @b;
  select @a != @b;
end;
//
delimiter ;//
call t_sysdate();
drop procedure t_sysdate;

#
# Bug #13534: timestampdiff() returned incorrect results across leap years
#
select timestampdiff(month,'2004-09-11','2004-09-11');
select timestampdiff(month,'2004-09-11','2005-09-11');
select timestampdiff(month,'2004-09-11','2006-09-11');
select timestampdiff(month,'2004-09-11','2007-09-11');
select timestampdiff(month,'2005-09-11','2004-09-11');
select timestampdiff(month,'2005-09-11','2003-09-11');

select timestampdiff(month,'2004-02-28','2005-02-28');
select timestampdiff(month,'2004-02-29','2005-02-28');
select timestampdiff(month,'2004-02-28','2005-02-28');
select timestampdiff(month,'2004-03-29','2005-03-28');
select timestampdiff(month,'2003-02-28','2004-02-29');
select timestampdiff(month,'2003-02-28','2005-02-28');

select timestampdiff(month,'1999-09-11','2001-10-10');
select timestampdiff(month,'1999-09-11','2001-9-11');

select timestampdiff(year,'1999-09-11','2001-9-11');
select timestampdiff(year,'2004-02-28','2005-02-28');
select timestampdiff(year,'2004-02-29','2005-02-28');

#
# Bug #18618: BETWEEN for dates with the second argument being a constant
#             expression and the first and the third arguments being fields 
#

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;
SELECT * FROM t1, t2 
  WHERE CAST(t1.day AS DATE) BETWEEN 
                             '2005.09.01' - INTERVAL 6 MONTH AND t2.day;
 
DROP TABLE t1,t2;

# End of 5.0 tests

# Restore timezone to default
set time_zone= @@global.time_zone;

#
# 21913: DATE_FORMAT() Crashes mysql server if I use it through
#        mysql-connector-j driver.
#

SET NAMES latin1;
SET character_set_results = NULL;
SHOW VARIABLES LIKE '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;

DROP TABLE testBug8868;

SET NAMES DEFAULT;

#
# Bug #22229: bug in DATE_ADD()
#

select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;

#
# Bug #21103: DATE column not compared as DATE
#

create table t1 (field DATE);
insert into t1 values ('2006-11-06');
select * from t1 where field < '2006-11-06 04:08:36.0'; 
select * from t1 where field = '2006-11-06 04:08:36.0'; 
select * from t1 where field = '2006-11-06'; 
select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
drop table t1;

#
# Bug #25643: SEC_TO_TIME function problem
#
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;
SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
  FROM t1 ORDER BY a DESC;
DROP TABLE t1;

#
# Bug #20293: group by cuts off value from time_format
#
# Check if using GROUP BY with TIME_FORMAT() produces correct results

SELECT TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") FROM (SELECT 3020399 AS a UNION SELECT 3020398 ) x GROUP BY 1;

#
# Bug#28875 Conversion between ASCII and LATIN1 charsets does not function
#
set names latin1;
create table t1 (a varchar(15) character set ascii not null);
insert into t1 values ('070514-000000');
# Conversion of date_format() result to ASCII
# is safe with the default locale en_US
--replace_column 1 #
select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
# Error for swe7: it is not ASCII compatible
set names swe7;
--error 1267
select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
set names latin1;
# Conversion of date_format() result to ASCII
# is not safe with the non-default locale fr_FR
# because month and day names can have accented characters
set lc_time_names=fr_FR;
--error 1267
select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;
set lc_time_names=en_US;
drop table t1;

--echo End of 5.0 tests