~drizzle-trunk/drizzle/development

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

select strcmp(localtime(),concat(current_date()," ",current_time()));

strcmp(localtime(),concat(current_date()," ",current_time()))

select strcmp(localtimestamp(),concat(current_date()," ",current_time()));

strcmp(localtimestamp(),concat(current_date()," ",current_time()))

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

319

300

SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;

320

301

quarter

321

302

322

SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);

323

DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)

324

9999-12-31 00:00:00

325

SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);

326

DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)

327

9999-12-31 00:00:00

328

303

SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;

329

304

"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND

330

305

1968-01-20 03:14:08

459

434

select unix_timestamp('2038-01-19 07:14:07');

460

435

unix_timestamp('2038-01-19 07:14:07')

461

436

462

CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);

463

INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08");

437

CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date);

438

INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02");

464

439

SELECT * from t1;

465

datetime timestamp date time

466

2001-01-02 03:04:05 2002-01-02 03:04:05 2003-01-02 06:07:08

440

datetime timestamp date

441

2001-01-02 03:04:05 2002-01-02 03:04:05 2003-01-02

467

442

select date_add("1997-12-31",INTERVAL 1 SECOND);

468

443

date_add("1997-12-31",INTERVAL 1 SECOND)

469

444

1997-12-31 00:00:01

644

619

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');

645

620

TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29')

646

621

647

select date_add(time,INTERVAL 1 SECOND) from t1;

648

date_add(time,INTERVAL 1 SECOND)

649

NULL

650

Warnings:

651

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

652

622

drop table t1;

653

623

select last_day('2000-02-05');

654

624

last_day('2000-02-05')

699

669

select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;

700

670

strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0

701

671

702

select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;

703

strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0

704

705

672

select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;

706

673

strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0

707

674

708

select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;

709

strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0

710

711

675

select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;

712

676

strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0

713

677

714

select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;

715

strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0

716

717

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

678

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

718

679

id select_type table type possible_keys key key_len ref rows filtered Extra

719

680

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

720

681

Warnings:

721

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)`

682

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()`,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)`

722

683

SET @TMP='2007-08-01 12:22:49';

723

684

CREATE TABLE t1 (d DATETIME);

724

685

INSERT INTO t1 VALUES ('2007-08-01 12:22:59');

734

695

ERROR HY000: Received an invalid datetime value '2005-00-01'.

735

696

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

736

697

ERROR HY000: Received an invalid datetime value '2005-01-00'.

737

select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),

738

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

739

ERROR HY000: Received an invalid datetime value '0000-01-00'.

740

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

741

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

742

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

698

create table t1(f1 date, f3 datetime);

699

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

700

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

743

701

select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);

744

702

745

703

2006-01-01

749

707

select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);

750

708

751

709

2006-01-01

752

select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);

753

754

12:01:02

755

select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);

756

757

12:01:02

758

710

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

759

711

760

712

2006-01-01 12:01:01

779

731

780

732

2006-01-02

781

733

drop table t1;

782

create table t1 select now() - now(), curtime() - curtime(),

783

sec_to_time(1) + 0, from_unixtime(1) + 0;

784

show create table t1;

785

Table Create Table

786

t1 CREATE TABLE `t1` (

787

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

788

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

789

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

790

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

791

) ENGINE=InnoDB

792

drop table t1;

793

SELECT SEC_TO_TIME(3300000);

794

SEC_TO_TIME(3300000)

795

838:59:59

796

Warnings:

797

Warning 1292 Truncated incorrect time value: '3300000'

798

SELECT SEC_TO_TIME(3300000)+0;

799

SEC_TO_TIME(3300000)+0

800

8385959.000000

801

Warnings:

802

Warning 1292 Truncated incorrect time value: '3300000'

803

SELECT SEC_TO_TIME(3600 * 4294967296);

804

SEC_TO_TIME(3600 * 4294967296)

805

838:59:59

806

Warnings:

807

Warning 1292 Truncated incorrect time value: '15461882265600'

808

SELECT TIME_TO_SEC('916:40:00');

809

TIME_TO_SEC('916:40:00')

810

3020399

811

Warnings:

812

Warning 1292 Truncated incorrect time value: '916:40:00'

813

SELECT ADDTIME('500:00:00', '416:40:00');

814

ADDTIME('500:00:00', '416:40:00')

815

838:59:59

816

Warnings:

817

Warning 1292 Truncated incorrect time value: '916:40:00'

818

SELECT ADDTIME('916:40:00', '416:40:00');

819

ADDTIME('916:40:00', '416:40:00')

820

838:59:59

821

Warnings:

822

Warning 1292 Truncated incorrect time value: '916:40:00'

823

Warning 1292 Truncated incorrect time value: '1255:39:59'

824

SELECT SUBTIME('916:40:00', '416:40:00');

825

SUBTIME('916:40:00', '416:40:00')

826

422:19:59

827

Warnings:

828

Warning 1292 Truncated incorrect time value: '916:40:00'

829

SELECT SUBTIME('-916:40:00', '416:40:00');

830

SUBTIME('-916:40:00', '416:40:00')

831

-838:59:59

832

Warnings:

833

Warning 1292 Truncated incorrect time value: '-916:40:00'

834

Warning 1292 Truncated incorrect time value: '-1255:39:59'

835

SELECT MAKETIME(916,0,0);

836

MAKETIME(916,0,0)

837

838:59:59

838

Warnings:

839

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

840

SELECT MAKETIME(4294967296, 0, 0);

841

MAKETIME(4294967296, 0, 0)

842

838:59:59

843

Warnings:

844

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

845

SELECT MAKETIME(-4294967296, 0, 0);

846

MAKETIME(-4294967296, 0, 0)

847

-838:59:59

848

Warnings:

849

Warning 1292 Truncated incorrect time value: '-4294967296:00:00'

850

SELECT MAKETIME(0, 4294967296, 0);

851

MAKETIME(0, 4294967296, 0)

852

NULL

853

SELECT MAKETIME(0, 0, 4294967296);

854

MAKETIME(0, 0, 4294967296)

855

NULL

856

734

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

857

735

ERROR HY000: Received an invalid datetime value '100000:02:03'.

858

CREATE TABLE t1(f1 TIME);

859

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

860

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

861

SELECT * FROM t1;

862

863

DROP TABLE t1;

864

736

SHOW VARIABLES LIKE 'character_set_results';

865

737

Variable_name Value

866

738

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

869

741

fmtddate field2

870

742

Sep-4 12:00AM abcd

871

743

DROP TABLE testBug8868;

872

CREATE TABLE t1 (

873

a TIMESTAMP

874

);

875

INSERT INTO t1 VALUES (now()), (now());

876

SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a);

877

878

879

880

DROP TABLE t1;

881

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H)

882

union

883

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H);

884

885

120

886

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H)

887

union

888

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H);

889

890

120

891

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H)

892

union

893

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H);

894

895

896

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H)

897

union

898

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H);

899

900

901

744

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

902

745

ERROR HY000: Received an invalid datetime value '0000-00-00'.

903

746

End of 4.1 tests

986

829

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

987

830

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

988

831

DROP TABLE t1,t2;

989

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

990

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

991

NULL

992

832

create table t1 (field DATE);

993

833

insert into t1 values ('2006-11-06');

994

834

select * from t1 where field < '2006-11-06 04:08:36.0';

1006

846

field

1007

847

2006-11-06

1008

848

drop table t1;

1009

CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a));

1010

INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL),

1011

(2, '11:00:00', '11:15:00', '1972-02-06');

1012

SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)

1013

FROM t1;

1014

t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d)

1015

10:00:00 NULL NULL NULL

1016

11:00:00 11:15:00 00:15:00 NULL

1017

SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)

1018

FROM t1 ORDER BY a DESC;

1019

t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d)

1020

11:00:00 11:15:00 00:15:00 1

1021

10:00:00 NULL NULL NULL

1022

DROP TABLE t1;

1023

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

1024

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

1025

838:59:58

1026

838:59:59

1027

849

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

1028

850

DATE_ADD('20071108181000', INTERVAL 1 DAY)

1029

851

2007-11-09 18:10:00

Older »