~drizzle-trunk/drizzle/development

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

413

414

SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;

414

415

"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND

415

416

8895-03-27 22:11:40

417

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;

418

"1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND

419

6255-04-08 15:04:32

420

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;

421

"1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE

422

5983-01-24 02:08:00

423

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR;

424

"1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR

425

2019-08-15 16:00:00

426

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;

427

"1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND

428

NULL

429

Warnings:

430

Warning 1441 Datetime function: datetime field overflow

431

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;

432

"1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE

433

NULL

434

Warnings:

435

Warning 1441 Datetime function: datetime field overflow

436

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;

437

"1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR

438

NULL

439

Warnings:

440

Warning 1441 Datetime function: datetime field overflow

416

441

SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;

417

442

"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND

418

443

NULL

641

666

select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;

642

667

timestampadd(SQL_TSI_FRAC_SECOND, 1, date)

643

668

2003-01-02 00:00:00.000001

669

Warnings:

670

Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead

644

671

select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;

645

672

646

673

674

701

select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;

675

702

676

703

7689538999999

704

Warnings:

705

Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead

677

706

select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,

678

707

timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,

679

708

timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,

770

799

from_days(to_days("960101")) as b;

771

800

describe t1;

772

801

Field Type Null Key Default Extra

773

a date NO NULL

802

a date NO 0000-00-00

774

803

b date YES NULL

775

804

select * from t1;

776

805

a b

843

872

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

844

873

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

845

874

NULL NULL January NULL

875

set time_zone='-6:00';

876

create table t1(a timestamp);

877

insert into t1 values (19691231190001);

878

select * from t1;

879

880

1969-12-31 19:00:01

881

drop table t1;

846

882

create table t1(f1 date, f2 time, f3 datetime);

847

883

insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");

848

884

insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");

890

926

show create table t1;

891

927

Table Create Table

892

928

t1 CREATE TABLE `t1` (

893

`now() - now()` double(23,6),

894

`curtime() - curtime()` double(23,6),

895

`sec_to_time(1) + 0` double(23,6),

896

`from_unixtime(1) + 0` double(23,6)

897

) ENGINE=InnoDB

929

`now() - now()` double(23,6) NOT NULL DEFAULT '0.000000',

930

`curtime() - curtime()` double(23,6) NOT NULL DEFAULT '0.000000',

931

`sec_to_time(1) + 0` double(23,6) DEFAULT NULL,

932

`from_unixtime(1) + 0` double(23,6) DEFAULT NULL

933

) ENGINE=MyISAM DEFAULT CHARSET=latin1

898

934

drop table t1;

899

935

SELECT SEC_TO_TIME(3300000);

900

936

SEC_TO_TIME(3300000)

959

995

SELECT MAKETIME(0, 0, 4294967296);

960

996

MAKETIME(0, 0, 4294967296)

961

997

NULL

998

SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0);

999

MAKETIME(CAST(-1 AS UNSIGNED), 0, 0)

1000

838:59:59

1001

Warnings:

1002

Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00'

962

1003

SELECT EXTRACT(HOUR FROM '100000:02:03');

963

1004

EXTRACT(HOUR FROM '100000:02:03')

964

1005

838

966

1007

Warning 1292 Truncated incorrect time value: '100000:02:03'

967

1008

CREATE TABLE t1(f1 TIME);

968

1009

INSERT INTO t1 VALUES('916:00:00 a');

969

ERROR 22007: Incorrect time value: '916:00:00 a' for column 'f1' at row 1

1010

Warnings:

1011

Warning 1265 Data truncated for column 'f1' at row 1

1012

Warning 1264 Out of range value for column 'f1' at row 1

970

1013

SELECT * FROM t1;

971

1014

1015

838:59:59

972

1016

DROP TABLE t1;

1017

SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED));

1018

SEC_TO_TIME(CAST(-1 AS UNSIGNED))

1019

838:59:59

1020

Warnings:

1021

Warning 1292 Truncated incorrect time value: '18446744073709551615'

1022

SET NAMES latin1;

1023

SET character_set_results = NULL;

973

1024

SHOW VARIABLES LIKE 'character_set_results';

974

1025

Variable_name Value

975

CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32));

1026

character_set_results

1027

CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY);

976

1028

INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');

977

1029

SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;

978

1030

fmtddate field2

979

1031

Sep-4 12:00AM abcd

980

1032

DROP TABLE testBug8868;

1033

SET NAMES DEFAULT;

981

1034

CREATE TABLE t1 (

982

1035

a TIMESTAMP

983

1036

);

1020

1073

id select_type table type possible_keys key key_len ref rows filtered Extra

1021

1074

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used

1022

1075

Warnings:

1076

Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead

1023

1077

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`

1024

1078

select time_format('100:00:00', '%H %k %h %I %l');

1025

1079

time_format('100:00:00', '%H %k %h %I %l')

1026

1080

100 100 04 04 4

1081

SET GLOBAL log_bin_trust_function_creators = 1;

1082

create table t1 (a timestamp default '2005-05-05 01:01:01',

1083

b timestamp default '2005-05-05 01:01:01');

1084

drop function if exists t_slow_sysdate;

1085

create function t_slow_sysdate() returns timestamp

1086

begin

1087

do sleep(2);

1088

return sysdate();

1089

end;

1090

1091

insert into t1 set a = sysdate(), b = t_slow_sysdate();//

1092

create trigger t_before before insert on t1

1093

for each row begin

1094

set new.b = t_slow_sysdate();

1095

end

1096

1097

insert into t1 set a = sysdate();

1098

select a != b from t1;

1099

a != b

1100

1101

1102

drop trigger t_before;

1103

drop function t_slow_sysdate;

1104

drop table t1;

1105

SET GLOBAL log_bin_trust_function_creators = 0;

1106

create table t1 (a datetime, i int, b datetime);

1107

insert into t1 select sysdate(), sleep(1), sysdate() from dual;

1108

select a != b from t1;

1109

a != b

1110

1111

drop table t1;

1112

create procedure t_sysdate()

1113

begin

1114

select sysdate() into @a;

1115

do sleep(2);

1116

select sysdate() into @b;

1117

select @a != @b;

1118

end;

1119

1120

call t_sysdate();

1121

@a != @b

1122

1123

drop procedure t_sysdate;

1027

1124

select timestampdiff(month,'2004-09-11','2004-09-11');

1028

1125

timestampdiff(month,'2004-09-11','2004-09-11')

1029

1126

1100

1197

1 2005-06-01 3 2005-07-15

1101

1198

3 2005-07-01 3 2005-07-15

1102

1199

DROP TABLE t1,t2;

1200

set time_zone= @@global.time_zone;

1103

1201

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

1104

1202

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

1105

1203

NULL

1138

1236

TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s")

1139

1237

838:59:58

1140

1238

838:59:59

1239

set names latin1;

1240

create table t1 (a varchar(15) character set ascii not null);

1241

insert into t1 values ('070514-000000');

1242

select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;

1243

concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull'))

1244

1245

set names swe7;

1246

select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;

1247

ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (swe7_swedish_ci,COERCIBLE) for operation 'concat'

1248

set names latin1;

1249

set lc_time_names=fr_FR;

1250

select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1;

1251

ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'concat'

1252

set lc_time_names=en_US;

1253

drop table t1;

1141

1254

select DATE_ADD('20071108181000', INTERVAL 1 DAY);

1142

1255

DATE_ADD('20071108181000', INTERVAL 1 DAY)

1143

1256

2007-11-09 18:10:00

1156

1269

SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18');

1157

1270

TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18')

1158

1271

2008-02-18 00:00:00.000001

1272

Warnings:

1273

Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead

1159

1274

SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18');

1160

1275

TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18')

1161

1276

86400000000

1277

Warnings:

1278

Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead

1162

1279

SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);

1163

1280

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND)' at line 1

1164

1281

SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND);

Older »