test/sql/ScalarFunction/exp.test040.ksh

   1 echo Create table t1(f1 timestamp, f2 timestamp);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 echo select * from t1;
   9 ---------------------------------------------------------
  10         t1.f1   t1.f2
  11 ---------------------------------------------------------
  12         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
  13         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
  14         2003/1/3 1:1:1.0        2003/7/15 12:30:30.0
  15         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
  16         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
  17
  18 echo select * from t1 where extract(hour from f2) =12;
  19 ---------------------------------------------------------
  20         t1.f1   t1.f2
  21 ---------------------------------------------------------
  22         2003/1/3 1:1:1.0        2003/7/15 12:30:30.0
  23
  24 echo select * from t1 where extract(hour from f2)<=12;
  25 ---------------------------------------------------------
  26         t1.f1   t1.f2
  27 ---------------------------------------------------------
  28         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
  29         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
  30         2003/1/3 1:1:1.0        2003/7/15 12:30:30.0
  31
  32 echo select * from t1 where extract(hour from f2) >12;
  33 ---------------------------------------------------------
  34         t1.f1   t1.f2
  35 ---------------------------------------------------------
  36         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
  37         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
  38
  39 echo select * from t1 where extract(hour from f2)!=12;
  40 ---------------------------------------------------------
  41         t1.f1   t1.f2
  42 ---------------------------------------------------------
  43         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
  44         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
  45         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
  46         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
  47
  48 echo select * from t1 where extract(minute from f2) =30;
  49 ---------------------------------------------------------
  50         t1.f1   t1.f2
  51 ---------------------------------------------------------
  52         2003/1/3 1:1:1.0        2003/7/15 12:30:30.0
  53
  54 echo select * from t1 where extract(minute from f2) <30;
  55 ---------------------------------------------------------
  56         t1.f1   t1.f2
  57 ---------------------------------------------------------
  58         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
  59         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
  60
  61 echo select * from t1 where extract(minute from f2)>=30;
  62 ---------------------------------------------------------
  63         t1.f1   t1.f2
  64 ---------------------------------------------------------
  65         2003/1/3 1:1:1.0        2003/7/15 12:30:30.0
  66         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
  67         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
  68
  69 echo select * from t1 where extract(minute from f2)!=30;
  70 ---------------------------------------------------------
  71         t1.f1   t1.f2
  72 ---------------------------------------------------------
  73         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
  74         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
  75         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
  76         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
  77
  78 echo select * from t1 where extract(second from f2) =30;
  79 ---------------------------------------------------------
  80         t1.f1   t1.f2
  81 ---------------------------------------------------------
  82         2003/1/3 1:1:1.0        2003/7/15 12:30:30.0
  83
  84 echo select * from t1 where extract(second from f2) <30;
  85 ---------------------------------------------------------
  86         t1.f1   t1.f2
  87 ---------------------------------------------------------
  88         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
  89         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
  90
  91 echo select * from t1 where extract(second from f2) >30;
  92 ---------------------------------------------------------
  93         t1.f1   t1.f2
  94 ---------------------------------------------------------
  95         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
  96         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
  97
  98 echo select * from t1 where extract(second from f2)<>30;
  99 ---------------------------------------------------------
 100         t1.f1   t1.f2
 101 ---------------------------------------------------------
 102         2001/1/1 1:1:1.0        2001/1/5 1:1:1.0
 103         2002/1/1 1:1:1.0        2002/4/10 2:2:2.0
 104         2004/1/1 1:1:1.0        2004/10/20 22:58:58.0
 105         2005/1/1 1:1:1.0        2005/12/25 23:59:59.0
 106
 107 Statement Executed