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