github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_date.test (about)

     1  #各种数据类型
     2  SELECT DATE(0.0124);
     3  SELECT DATE("2112123");
     4  SELECT DATE(1231241.4513);
     5  SELECT DATE("2017-06-15");
     6  
     7  
     8  SELECT DATE("2017-06-15 09:34:21");
     9  
    10  SELECT DATE("The date is 2017-06-15");
    11  
    12  
    13  SELECT DATE('2008-05-17 11:31:31') as required_DATE;
    14  
    15  
    16  #NULL值
    17  select coalesce(date(NULL)), coalesce(cast(NULL as DATE));
    18  select date(NULL);
    19  
    20  
    21  #函数嵌套
    22  
    23  CREATE TABLE t1 (d1 datetime);
    24  INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),
    25  ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00');
    26  SELECT cast(date(d1) as signed) FROM t1;
    27  SELECT d1 % 7 FROM t1;
    28  drop table t1;
    29  
    30  
    31  #函数嵌套
    32  
    33  CREATE TABLE t1 (d1 datetime);
    34  INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),
    35  ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00');
    36  SELECT sum(date(d1)) FROM t1;
    37  drop table t1;
    38  
    39  
    40  #函数嵌套
    41  #CREATE TABLE t1 AS
    42  #SELECT
    43  #DATE_ADD('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
    44  #DATE_ADD('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
    45  #DATE_ADD(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
    46  #DATE_ADD(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
    47  #SELECT * FROM t1;
    48  #DROP TABLE t1;
    49  
    50  
    51  #EXTREME VALUES
    52  
    53  select date("1997-12-31 23:59:59.000001");
    54  
    55  
    56  select date("1997-13-31 23:59:59.000001");
    57  SELECT DATE(20110512154559.6 + 0e0);
    58  
    59  SELECT DATE(concat_ws('a', 0));
    60  
    61  
    62  #timestamp type
    63  CREATE TABLE t1 (a timestamp);
    64  INSERT INTO t1 VALUES ("2020-12-31 12:01:32");
    65  SELECT DATE(MIN(a)) FROM t1;
    66  DROP TABLE t1;
    67  
    68  
    69  #逻辑比较
    70  
    71  CREATE TABLE t1
    72  (first_usage DATE, last_recharge DATETIME, life_time SMALLINT(4) UNSIGNED);
    73  INSERT INTO t1 VALUES ('2011-04-27', null, 900);
    74  SELECT
    75  DATE_ADD(coalesce(last_recharge, first_usage), INTERVAL life_time DAY ) as dt,
    76  DATE_ADD(coalesce(last_recharge, first_usage), INTERVAL life_time DAY ) <
    77  DATE('2011-04-28') as exp FROM t1;
    78  DROP TABLE t1;
    79  
    80  
    81  
    82  #EXTREME VALUES
    83  SELECT DATE(20110512154559.616), DATE(FLOOR(20110512154559.616));
    84  
    85  #各种数据类型
    86  DROP TABLE IF EXISTS t3;
    87  CREATE TABLE t3(c1 DATE NOT NULL);
    88  INSERT INTO t3 VALUES('2000-01-01');
    89  INSERT INTO t3 VALUES('1999-12-31');
    90  INSERT INTO t3 VALUES('2000-01-01');
    91  INSERT INTO t3 VALUES('2006-12-25');
    92  INSERT INTO t3 VALUES('2008-02-29');
    93  SELECT DATE(c1) FROM t3;
    94  DROP TABLE t3;
    95  CREATE TABLE t3(c1 DATETIME NOT NULL);
    96  INSERT INTO t3 VALUES('2000-01-01');
    97  INSERT INTO t3 VALUES('1999-12-31');
    98  INSERT INTO t3 VALUES('2000-01-01');
    99  INSERT INTO t3 VALUES('2006-12-25');
   100  INSERT INTO t3 VALUES('2008-02-29');
   101  SELECT DATE(c1) FROM t3;
   102  DROP TABLE t3;
   103  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   104  INSERT INTO t3 VALUES('2000-01-01');
   105  INSERT INTO t3 VALUES('1999-12-31');
   106  INSERT INTO t3 VALUES('2000-01-01');
   107  INSERT INTO t3 VALUES('2006-12-25');
   108  INSERT INTO t3 VALUES('2008-02-29');
   109  SELECT DATE(c1) FROM t3;
   110  DROP TABLE t3;
   111  
   112  #0.5 not supported
   113  #/* 
   114  #CREATE TABLE t3(c1 DATE NOT NULL);
   115  #INSERT INTO t3 VALUES('2000-01-01');
   116  #INSERT INTO t3 VALUES('1999-12-31');
   117  #INSERT INTO t3 VALUES('2000-01-01');
   118  #INSERT INTO t3 VALUES('2006-12-25');
   119  #INSERT INTO t3 VALUES('2008-02-29');
   120  #SELECT DAYNAME(c1) FROM t3;
   121  #DROP TABLE t3;
   122  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   123  #INSERT INTO t3 VALUES('2000-01-01');
   124  #INSERT INTO t3 VALUES('1999-12-31');
   125  #INSERT INTO t3 VALUES('2000-01-01');
   126  #INSERT INTO t3 VALUES('2006-12-25');
   127  #INSERT INTO t3 VALUES('2008-02-29');
   128  #SELECT DAYNAME(c1) FROM t3;
   129  #DROP TABLE t3;
   130  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   131  #INSERT INTO t3 VALUES('2000-01-01');
   132  #INSERT INTO t3 VALUES('1999-12-31');
   133  #INSERT INTO t3 VALUES('2000-01-01');
   134  #INSERT INTO t3 VALUES('2006-12-25');
   135  #INSERT INTO t3 VALUES('2008-02-29');
   136  #SELECT DAYNAME(c1) FROM t3;
   137  #DROP TABLE t3;
   138  #CREATE TABLE t3(c1 DATE NOT NULL);
   139  #INSERT INTO t3 VALUES('2000-01-01');
   140  #INSERT INTO t3 VALUES('1999-12-31');
   141  #INSERT INTO t3 VALUES('2000-01-01');
   142  #INSERT INTO t3 VALUES('2006-12-25');
   143  #INSERT INTO t3 VALUES('2008-02-29');
   144  #SELECT DAYOFMONTH(c1) FROM t3;
   145  #DROP TABLE t3;
   146  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   147  #INSERT INTO t3 VALUES('2000-01-01');
   148  #INSERT INTO t3 VALUES('1999-12-31');
   149  #INSERT INTO t3 VALUES('2000-01-01');
   150  #INSERT INTO t3 VALUES('2006-12-25');
   151  #INSERT INTO t3 VALUES('2008-02-29');
   152  #SELECT DAYOFMONTH(c1) FROM t3;
   153  #DROP TABLE t3;
   154  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   155  #INSERT INTO t3 VALUES('2000-01-01');
   156  #INSERT INTO t3 VALUES('1999-12-31');
   157  #INSERT INTO t3 VALUES('2000-01-01');
   158  #INSERT INTO t3 VALUES('2006-12-25');
   159  #INSERT INTO t3 VALUES('2008-02-29');
   160  #SELECT DAYOFMONTH(c1) FROM t3;
   161  #DROP TABLE t3;
   162  #CREATE TABLE t3(c1 DATE NOT NULL);
   163  #INSERT INTO t3 VALUES('2000-01-01');
   164  #INSERT INTO t3 VALUES('1999-12-31');
   165  #INSERT INTO t3 VALUES('2000-01-01');
   166  #INSERT INTO t3 VALUES('2006-12-25');
   167  #INSERT INTO t3 VALUES('2008-02-29');
   168  #SELECT DAYOFWEEK(c1) FROM t3;
   169  #DROP TABLE t3;
   170  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   171  #INSERT INTO t3 VALUES('2000-01-01');
   172  #INSERT INTO t3 VALUES('1999-12-31');
   173  #INSERT INTO t3 VALUES('2000-01-01');
   174  #INSERT INTO t3 VALUES('2006-12-25');
   175  #INSERT INTO t3 VALUES('2008-02-29');
   176  #SELECT DAYOFWEEK(c1) FROM t3;
   177  #DROP TABLE t3;
   178  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   179  #INSERT INTO t3 VALUES('2000-01-01');
   180  #INSERT INTO t3 VALUES('1999-12-31');
   181  #INSERT INTO t3 VALUES('2000-01-01');
   182  #INSERT INTO t3 VALUES('2006-12-25');
   183  #INSERT INTO t3 VALUES('2008-02-29');
   184  #SELECT DAYOFWEEK(c1) FROM t3;
   185  #DROP TABLE t3; 
   186  #*/
   187  
   188  
   189  CREATE TABLE t3(c1 DATE NOT NULL);
   190  INSERT INTO t3 VALUES('2000-01-01');
   191  INSERT INTO t3 VALUES('1999-12-31');
   192  INSERT INTO t3 VALUES('2000-01-01');
   193  INSERT INTO t3 VALUES('2006-12-25');
   194  INSERT INTO t3 VALUES('2008-02-29');
   195  SELECT DAYOFYEAR(c1) FROM t3;
   196  DROP TABLE t3;
   197  
   198  CREATE TABLE t3(c1 DATETIME NOT NULL);
   199  INSERT INTO t3 VALUES('2000-01-01');
   200  INSERT INTO t3 VALUES('1999-12-31');
   201  INSERT INTO t3 VALUES('2000-01-01');
   202  INSERT INTO t3 VALUES('2006-12-25');
   203  INSERT INTO t3 VALUES('2008-02-29');
   204  SELECT DAYOFYEAR(c1) FROM t3;
   205  DROP TABLE t3;
   206  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   207  INSERT INTO t3 VALUES('2000-01-01');
   208  INSERT INTO t3 VALUES('1999-12-31');
   209  INSERT INTO t3 VALUES('2000-01-01');
   210  INSERT INTO t3 VALUES('2006-12-25');
   211  INSERT INTO t3 VALUES('2008-02-29');
   212  SELECT DAYOFYEAR(c1) FROM t3;
   213  DROP TABLE t3;
   214  -- @bvt:issue
   215  
   216  #0.5 not supported
   217  #/* 
   218  #CREATE TABLE t3(c1 DATE NOT NULL);
   219  #INSERT INTO t3 VALUES('2000-01-01');
   220  #INSERT INTO t3 VALUES('1999-12-31');
   221  #INSERT INTO t3 VALUES('2000-01-01');
   222  #INSERT INTO t3 VALUES('2006-12-25');
   223  #INSERT INTO t3 VALUES('2008-02-29');
   224  #SELECT FROM_DAYS(c1) FROM t3;
   225  #DROP TABLE t3;
   226  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   227  #INSERT INTO t3 VALUES('2000-01-01');
   228  #INSERT INTO t3 VALUES('1999-12-31');
   229  #INSERT INTO t3 VALUES('2000-01-01');
   230  #INSERT INTO t3 VALUES('2006-12-25');
   231  #INSERT INTO t3 VALUES('2008-02-29');
   232  #SELECT FROM_DAYS(c1) FROM t3;
   233  #DROP TABLE t3;
   234  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   235  #INSERT INTO t3 VALUES('2000-01-01');
   236  #INSERT INTO t3 VALUES('1999-12-31');
   237  #INSERT INTO t3 VALUES('2000-01-01');
   238  #INSERT INTO t3 VALUES('2006-12-25');
   239  #INSERT INTO t3 VALUES('2008-02-29');
   240  #SELECT FROM_DAYS(c1) FROM t3;
   241  #DROP TABLE t3;
   242  #CREATE TABLE t3(c1 DATE NOT NULL);
   243  #INSERT INTO t3 VALUES('2000-01-01');
   244  #INSERT INTO t3 VALUES('1999-12-31');
   245  #INSERT INTO t3 VALUES('2000-01-01');
   246  #INSERT INTO t3 VALUES('2006-12-25');
   247  #INSERT INTO t3 VALUES('2008-02-29');
   248  #SELECT LAST_DAY(c1) FROM t3;
   249  #DROP TABLE t3;
   250  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   251  #INSERT INTO t3 VALUES('2000-01-01');
   252  #INSERT INTO t3 VALUES('1999-12-31');
   253  #INSERT INTO t3 VALUES('2000-01-01');
   254  #INSERT INTO t3 VALUES('2006-12-25');
   255  #INSERT INTO t3 VALUES('2008-02-29');
   256  #SELECT LAST_DAY(c1) FROM t3;
   257  #DROP TABLE t3;
   258  #*/
   259  
   260  CREATE TABLE t3(c1 DATE NOT NULL);
   261  INSERT INTO t3 VALUES('2000-01-01');
   262  INSERT INTO t3 VALUES('1999-12-31');
   263  INSERT INTO t3 VALUES('2000-01-01');
   264  INSERT INTO t3 VALUES('2006-12-25');
   265  INSERT INTO t3 VALUES('2008-02-29');
   266  SELECT MONTH(c1) FROM t3;
   267  DROP TABLE t3;
   268  CREATE TABLE t3(c1 DATETIME NOT NULL);
   269  INSERT INTO t3 VALUES('2000-01-01');
   270  INSERT INTO t3 VALUES('1999-12-31');
   271  INSERT INTO t3 VALUES('2000-01-01');
   272  INSERT INTO t3 VALUES('2006-12-25');
   273  INSERT INTO t3 VALUES('2008-02-29');
   274  SELECT MONTH(c1) FROM t3;
   275  DROP TABLE t3;
   276  
   277  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   278  INSERT INTO t3 VALUES('2000-01-01');
   279  INSERT INTO t3 VALUES('1999-12-31');
   280  INSERT INTO t3 VALUES('2000-01-01');
   281  INSERT INTO t3 VALUES('2006-12-25');
   282  INSERT INTO t3 VALUES('2008-02-29');
   283  SELECT MONTH(c1) FROM t3;
   284  DROP TABLE t3;
   285  
   286  
   287  #0.5 not supported
   288  #/* 
   289  #CREATE TABLE t3(c1 DATE NOT NULL);
   290  #INSERT INTO t3 VALUES('2000-01-01');
   291  #INSERT INTO t3 VALUES('1999-12-31');
   292  #INSERT INTO t3 VALUES('2000-01-01');
   293  #INSERT INTO t3 VALUES('2006-12-25');
   294  #INSERT INTO t3 VALUES('2008-02-29');
   295  #SELECT MONTHNAME(c1) FROM t3;
   296  #DROP TABLE t3;
   297  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   298  #INSERT INTO t3 VALUES('2000-01-01');
   299  #INSERT INTO t3 VALUES('1999-12-31');
   300  #INSERT INTO t3 VALUES('2000-01-01');
   301  #INSERT INTO t3 VALUES('2006-12-25');
   302  #INSERT INTO t3 VALUES('2008-02-29');
   303  #SELECT MONTHNAME(c1) FROM t3;
   304  #DROP TABLE t3;
   305  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   306  #INSERT INTO t3 VALUES('2000-01-01');
   307  #INSERT INTO t3 VALUES('1999-12-31');
   308  #INSERT INTO t3 VALUES('2000-01-01');
   309  #INSERT INTO t3 VALUES('2006-12-25');
   310  #INSERT INTO t3 VALUES('2008-02-29');
   311  #SELECT MONTHNAME(c1) FROM t3;
   312  #DROP TABLE t3;
   313  #CREATE TABLE t3(c1 DATE NOT NULL);
   314  #INSERT INTO t3 VALUES('2000-01-01');
   315  #INSERT INTO t3 VALUES('1999-12-31');
   316  #INSERT INTO t3 VALUES('2000-01-01');
   317  #INSERT INTO t3 VALUES('2006-12-25');
   318  #INSERT INTO t3 VALUES('2008-02-29');
   319  #SELECT QUARTER(c1) FROM t3;
   320  #DROP TABLE t3;
   321  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   322  #INSERT INTO t3 VALUES('2000-01-01');
   323  #INSERT INTO t3 VALUES('1999-12-31');
   324  #INSERT INTO t3 VALUES('2000-01-01');
   325  #INSERT INTO t3 VALUES('2006-12-25');
   326  #INSERT INTO t3 VALUES('2008-02-29');
   327  #SELECT QUARTER(c1) FROM t3;
   328  #DROP TABLE t3;
   329  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   330  #INSERT INTO t3 VALUES('2000-01-01');
   331  #INSERT INTO t3 VALUES('1999-12-31');
   332  #INSERT INTO t3 VALUES('2000-01-01');
   333  #INSERT INTO t3 VALUES('2006-12-25');
   334  #INSERT INTO t3 VALUES('2008-02-29');
   335  #SELECT QUARTER(c1) FROM t3;
   336  #DROP TABLE t3;
   337  #CREATE TABLE t3(c1 DATE NOT NULL);
   338  #INSERT INTO t3 VALUES('2000-01-01');
   339  #INSERT INTO t3 VALUES('1999-12-31');
   340  #INSERT INTO t3 VALUES('2000-01-01');
   341  #INSERT INTO t3 VALUES('2006-12-25');
   342  #INSERT INTO t3 VALUES('2008-02-29');
   343  #SELECT TIMESTAMP(c1) FROM t3;
   344  #DROP TABLE t3;
   345  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   346  #INSERT INTO t3 VALUES('2000-01-01');
   347  #INSERT INTO t3 VALUES('1999-12-31');
   348  #INSERT INTO t3 VALUES('2000-01-01');
   349  #INSERT INTO t3 VALUES('2006-12-25');
   350  #INSERT INTO t3 VALUES('2008-02-29');
   351  #SELECT TIMESTAMP(c1) FROM t3;
   352  #DROP TABLE t3;
   353  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   354  #INSERT INTO t3 VALUES('2000-01-01');
   355  #INSERT INTO t3 VALUES('1999-12-31');
   356  #INSERT INTO t3 VALUES('2000-01-01');
   357  #INSERT INTO t3 VALUES('2006-12-25');
   358  #INSERT INTO t3 VALUES('2008-02-29');
   359  #SELECT TIMESTAMP(c1) FROM t3;
   360  #DROP TABLE t3;
   361  #CREATE TABLE t3(c1 DATE NOT NULL);
   362  #INSERT INTO t3 VALUES('2000-01-01');
   363  #INSERT INTO t3 VALUES('1999-12-31');
   364  #INSERT INTO t3 VALUES('2000-01-01');
   365  #INSERT INTO t3 VALUES('2006-12-25');
   366  #INSERT INTO t3 VALUES('2008-02-29');
   367  #SELECT TO_DAYS(c1) FROM t3;
   368  #DROP TABLE t3;
   369  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   370  #INSERT INTO t3 VALUES('2000-01-01');
   371  #INSERT INTO t3 VALUES('1999-12-31');
   372  #INSERT INTO t3 VALUES('2000-01-01');
   373  #INSERT INTO t3 VALUES('2006-12-25');
   374  #INSERT INTO t3 VALUES('2008-02-29');
   375  #SELECT TO_DAYS(c1) FROM t3;
   376  #DROP TABLE t3;
   377  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   378  #INSERT INTO t3 VALUES('2000-01-01');
   379  #INSERT INTO t3 VALUES('1999-12-31');
   380  #INSERT INTO t3 VALUES('2000-01-01');
   381  #INSERT INTO t3 VALUES('2006-12-25');
   382  #INSERT INTO t3 VALUES('2008-02-29');
   383  #SELECT TO_DAYS(c1) FROM t3;
   384  #DROP TABLE t3;
   385  #CREATE TABLE t3(c1 DATE NOT NULL);
   386  #INSERT INTO t3 VALUES('2000-01-01');
   387  #INSERT INTO t3 VALUES('1999-12-31');
   388  #INSERT INTO t3 VALUES('2000-01-01');
   389  #INSERT INTO t3 VALUES('2006-12-25');
   390  #INSERT INTO t3 VALUES('2008-02-29');
   391  #SELECT WEEK(c1) FROM t3;
   392  #DROP TABLE t3;
   393  #CREATE TABLE t3(c1 DATETIME NOT NULL);
   394  #INSERT INTO t3 VALUES('2000-01-01');
   395  #INSERT INTO t3 VALUES('1999-12-31');
   396  #INSERT INTO t3 VALUES('2000-01-01');
   397  #INSERT INTO t3 VALUES('2006-12-25');
   398  #INSERT INTO t3 VALUES('2008-02-29');
   399  #SELECT WEEK(c1) FROM t3;
   400  #DROP TABLE t3;
   401  #CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   402  #INSERT INTO t3 VALUES('2000-01-01');
   403  #INSERT INTO t3 VALUES('1999-12-31');
   404  #INSERT INTO t3 VALUES('2000-01-01');
   405  #INSERT INTO t3 VALUES('2006-12-25');
   406  #INSERT INTO t3 VALUES('2008-02-29');
   407  #SELECT WEEK(c1) FROM t3;
   408  #DROP TABLE t3; 
   409  #*/
   410  
   411  CREATE TABLE t3(c1 DATE NOT NULL);
   412  INSERT INTO t3 VALUES('2000-01-01');
   413  INSERT INTO t3 VALUES('1999-12-31');
   414  INSERT INTO t3 VALUES('2000-01-01');
   415  INSERT INTO t3 VALUES('2006-12-25');
   416  INSERT INTO t3 VALUES('2008-02-29');
   417  SELECT WEEKDAY(c1) FROM t3;
   418  DROP TABLE t3;
   419  
   420  CREATE TABLE t3(c1 DATETIME NOT NULL);
   421  INSERT INTO t3 VALUES('2000-01-01');
   422  INSERT INTO t3 VALUES('1999-12-31');
   423  INSERT INTO t3 VALUES('2000-01-01');
   424  INSERT INTO t3 VALUES('2006-12-25');
   425  INSERT INTO t3 VALUES('2008-02-29');
   426  SELECT WEEKDAY(c1) FROM t3;
   427  DROP TABLE t3;
   428  
   429  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   430  INSERT INTO t3 VALUES('2000-01-01');
   431  INSERT INTO t3 VALUES('1999-12-31');
   432  INSERT INTO t3 VALUES('2000-01-01');
   433  INSERT INTO t3 VALUES('2006-12-25');
   434  INSERT INTO t3 VALUES('2008-02-29');
   435  SELECT WEEKDAY(c1) FROM t3;
   436  DROP TABLE t3;
   437  
   438  # /* CREATE TABLE t3(c1 DATE NOT NULL);
   439  # INSERT INTO t3 VALUES('2000-01-01');
   440  # INSERT INTO t3 VALUES('1999-12-31');
   441  # INSERT INTO t3 VALUES('2000-01-01');
   442  # INSERT INTO t3 VALUES('2006-12-25');
   443  # INSERT INTO t3 VALUES('2008-02-29');
   444  # SELECT WEEKOFYEAR(c1) FROM t3;
   445  # DROP TABLE t3;
   446  # CREATE TABLE t3(c1 DATETIME NOT NULL);
   447  # INSERT INTO t3 VALUES('2000-01-01');
   448  # INSERT INTO t3 VALUES('1999-12-31');
   449  # INSERT INTO t3 VALUES('2000-01-01');
   450  # INSERT INTO t3 VALUES('2006-12-25');
   451  # INSERT INTO t3 VALUES('2008-02-29');
   452  # SELECT WEEKOFYEAR(c1) FROM t3;
   453  # DROP TABLE t3;
   454  # CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   455  # INSERT INTO t3 VALUES('2000-01-01');
   456  # INSERT INTO t3 VALUES('1999-12-31');
   457  # INSERT INTO t3 VALUES('2000-01-01');
   458  # INSERT INTO t3 VALUES('2006-12-25');
   459  # INSERT INTO t3 VALUES('2008-02-29');
   460  # SELECT WEEKOFYEAR(c1) FROM t3;
   461  # DROP TABLE t3; */
   462  
   463  
   464  CREATE TABLE t3(c1 DATE NOT NULL);
   465  INSERT INTO t3 VALUES('2000-01-01');
   466  INSERT INTO t3 VALUES('1999-12-31');
   467  INSERT INTO t3 VALUES('2000-01-01');
   468  INSERT INTO t3 VALUES('2006-12-25');
   469  INSERT INTO t3 VALUES('2008-02-29');
   470  SELECT YEAR(c1) FROM t3;
   471  DROP TABLE t3;
   472  
   473  CREATE TABLE t3(c1 DATETIME NOT NULL);
   474  INSERT INTO t3 VALUES('2000-01-01');
   475  INSERT INTO t3 VALUES('1999-12-31');
   476  INSERT INTO t3 VALUES('2000-01-01');
   477  INSERT INTO t3 VALUES('2006-12-25');
   478  INSERT INTO t3 VALUES('2008-02-29');
   479  SELECT YEAR(c1) FROM t3;
   480  DROP TABLE t3;
   481  
   482  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   483  INSERT INTO t3 VALUES('2000-01-01');
   484  INSERT INTO t3 VALUES('1999-12-31');
   485  INSERT INTO t3 VALUES('2000-01-01');
   486  INSERT INTO t3 VALUES('2006-12-25');
   487  INSERT INTO t3 VALUES('2008-02-29');
   488  SELECT YEAR(c1) FROM t3;
   489  DROP TABLE t3;
   490  
   491  # /* CREATE TABLE t3(c1 DATE NOT NULL);
   492  # INSERT INTO t3 VALUES('2000-01-01');
   493  # INSERT INTO t3 VALUES('1999-12-31');
   494  # INSERT INTO t3 VALUES('2000-01-01');
   495  # INSERT INTO t3 VALUES('2006-12-25');
   496  # INSERT INTO t3 VALUES('2008-02-29');
   497  # SELECT YEARWEEK(c1) FROM t3;
   498  # DROP TABLE t3;
   499  # CREATE TABLE t3(c1 DATETIME NOT NULL);
   500  # INSERT INTO t3 VALUES('2000-01-01');
   501  # INSERT INTO t3 VALUES('1999-12-31');
   502  # INSERT INTO t3 VALUES('2000-01-01');
   503  # INSERT INTO t3 VALUES('2006-12-25');
   504  # INSERT INTO t3 VALUES('2008-02-29');
   505  # SELECT YEARWEEK(c1) FROM t3;
   506  # DROP TABLE t3;
   507  # CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   508  # INSERT INTO t3 VALUES('2000-01-01');
   509  # INSERT INTO t3 VALUES('1999-12-31');
   510  # INSERT INTO t3 VALUES('2000-01-01');
   511  # INSERT INTO t3 VALUES('2006-12-25');
   512  # INSERT INTO t3 VALUES('2008-02-29');
   513  # SELECT YEARWEEK(c1) FROM t3;
   514  # DROP TABLE t3; */
   515  
   516  #逻辑操作
   517  
   518  #SELECT date(now()) = date(utc_timestamp());
   519  
   520  
   521  
   522  SELECT date("2022-12-22 02:34:23") = date("2022-12-22 03:34:23");
   523  
   524  
   525  #insert & distinct
   526  
   527  drop table if exists t1;
   528  create table t1(a date);
   529  insert into t1 SELECT DATE("2017-06-15 09:34:21");
   530  insert into t1 SELECT DATE("2019-06-25 10:12:21");
   531  insert into t1 SELECT DATE("2019-06-25 18:20:49");
   532  select distinct a from t1;
   533  drop table t1;
   534  
   535  
   536  #HAVING & 算术运算
   537  
   538  drop table if exists t1;
   539  create table t1(a INT,  b datetime);
   540  insert into t1 values(1, "2017-06-15 09:34:21"),(1, "2019-06-25 10:12:21"),(2, "2019-06-25 18:20:49"),(3, "2019-06-25 18:20:49");
   541  select b from t1 group by b having date(b)>"2018-01-01";
   542  drop table t1;
   543  
   544  
   545  #WHERE
   546  
   547  drop table if exists t1;
   548  create table t1(a INT,  b date);
   549  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
   550  select * from t1 where date(b)!="2012-10-12";
   551  drop table t1;
   552  
   553  
   554  
   555  #ON CONDITION
   556  create table t1(a INT,  b date);
   557  create table t2(a INT,  b date);
   558  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
   559  insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12");
   560  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (date(t1.b) = date(t2.b));
   561  drop table t1;
   562  drop table t2;