~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

414

413

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

415

414

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

416

415

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

441

416

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

442

417

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

443

418

NULL

666

641

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

667

642

timestampadd(SQL_TSI_FRAC_SECOND, 1, date)

668

643

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

671

644

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

672

645

673

646

701

674

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

702

675

703

676

7689538999999

704

Warnings:

705

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

706

677

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

707

678

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

708

679

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

799

770

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

800

771

describe t1;

801

772

Field Type Null Key Default Extra

802

a date NO 0000-00-00

773

a date NO NULL

803

774

b date YES NULL

804

775

select * from t1;

805

776

a b

872

843

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

873

844

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

874

845

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;

882

846

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

883

847

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

884

848

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

926

890

show create table t1;

927

891

Table Create Table

928

892

t1 CREATE TABLE `t1` (

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

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

934

898

drop table t1;

935

899

SELECT SEC_TO_TIME(3300000);

936

900

SEC_TO_TIME(3300000)

995

959

SELECT MAKETIME(0, 0, 4294967296);

996

960

MAKETIME(0, 0, 4294967296)

997

961

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'

1003

962

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

1004

963

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

1005

964

838

1007

966

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

1008

967

CREATE TABLE t1(f1 TIME);

1009

968

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

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

969

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

1013

970

SELECT * FROM t1;

1014

971

1015

838:59:59

1016

972

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;

1024

973

SHOW VARIABLES LIKE 'character_set_results';

1025

974

Variable_name Value

1026

character_set_results

1027

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

975

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

1028

976

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

1029

977

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

1030

978

fmtddate field2

1031

979

Sep-4 12:00AM abcd

1032

980

DROP TABLE testBug8868;

1033

SET NAMES DEFAULT;

1034

981

CREATE TABLE t1 (

1035

982

a TIMESTAMP

1036

983

);

1073

1020

id select_type table type possible_keys key key_len ref rows filtered Extra

1074

1021

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

1075

1022

Warnings:

1076

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

1077

1023

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`

1078

1024

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

1079

1025

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

1080

1026

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;

1124

1027

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

1125

1028

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

1126

1029

1197

1100

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

1198

1101

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

1199

1102

DROP TABLE t1,t2;

1200

set time_zone= @@global.time_zone;

1201

1103

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

1202

1104

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

1203

1105

NULL

1236

1138

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

1237

1139

838:59:58

1238

1140

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;

1254

1141

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

1255

1142

DATE_ADD('20071108181000', INTERVAL 1 DAY)

1256

1143

2007-11-09 18:10:00

1269

1156

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

1270

1157

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

1271

1158

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

1274

1159

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

1275

1160

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

1276

1161

86400000000

1277

Warnings:

1278

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

1279

1162

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

1280

1163

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

1281

1164

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

Older »