github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/sem/tree/testdata/eval/extract (about)

     1  # Extract from dates.
     2  
     3  eval
     4  extract(year from '2010-09-28'::date)
     5  ----
     6  2010.0
     7  
     8  eval
     9  extract('year' from '2010-09-28'::date)
    10  ----
    11  2010.0
    12  
    13  eval
    14  extract('YEAR' from '2010-09-28'::date)
    15  ----
    16  2010.0
    17  
    18  eval
    19  extract(month from '2010-09-28'::date)
    20  ----
    21  9.0
    22  
    23  eval
    24  extract(day from '2010-09-28'::date)
    25  ----
    26  28.0
    27  
    28  eval
    29  extract(dayofyear from '2010-09-28'::date)
    30  ----
    31  271.0
    32  
    33  eval
    34  extract(week from '2010-01-14'::date)
    35  ----
    36  2.0
    37  
    38  eval
    39  extract(dayofweek from '2010-09-28'::date)
    40  ----
    41  2.0
    42  
    43  eval
    44  extract(isodow from '2010-09-28'::date)
    45  ----
    46  2.0
    47  
    48  eval
    49  extract(isodow from '2010-09-26'::date)
    50  ----
    51  7.0
    52  
    53  eval
    54  extract(quarter from '2010-09-28'::date)
    55  ----
    56  3.0
    57  
    58  eval
    59  extract(century from '2010-09-28'::date)
    60  ----
    61  21.0
    62  
    63  eval
    64  extract(century from '2010-09-28 BC'::date)
    65  ----
    66  -21.0
    67  
    68  eval
    69  extract(decade from '2010-09-28'::date)
    70  ----
    71  201.0
    72  
    73  eval
    74  extract(decade from '2010-09-28 BC'::date)
    75  ----
    76  -201.0
    77  
    78  eval
    79  extract(isoyear from '2006-01-01'::date)
    80  ----
    81  2005.0
    82  
    83  eval
    84  extract(isoyear from '2006-01-02'::date)
    85  ----
    86  2006.0
    87  
    88  eval
    89  extract(millennium from '2010-09-28'::date)
    90  ----
    91  3.0
    92  
    93  eval
    94  extract(millennium from '2010-09-28 BC'::date)
    95  ----
    96  -3.0
    97  
    98  eval
    99  extract(julian from '2010-09-28'::date)
   100  ----
   101  2.455468e+06
   102  
   103  eval
   104  extract(julian from '4714-11-24 BC'::date)
   105  ----
   106  0.0
   107  
   108  # Extract from times.
   109  
   110  eval
   111  extract(hour from '12:00:00'::time)
   112  ----
   113  12.0
   114  
   115  eval
   116  extract(minute from '12:30:00'::time)
   117  ----
   118  30.0
   119  
   120  eval
   121  extract(second from '12:00:30'::time)
   122  ----
   123  30.0
   124  
   125  eval
   126  extract(millisecond from '12:00:00.123456'::time)
   127  ----
   128  123.456
   129  
   130  eval
   131  extract(microsecond from '12:00:00.123456'::time)
   132  ----
   133  123456.0
   134  
   135  # Extract from timestamps.
   136  
   137  eval
   138  extract(year from '2010-09-28 12:13:14.1+00:00'::timestamp)
   139  ----
   140  2010.0
   141  
   142  eval
   143  extract(year from '2010-09-28 12:13:14.1+00:00'::timestamp)
   144  ----
   145  2010.0
   146  
   147  eval
   148  extract(month from '2010-09-28 12:13:14.1+00:00'::timestamp)
   149  ----
   150  9.0
   151  
   152  eval
   153  extract(day from '2010-09-28 12:13:14.1+00:00'::timestamp)
   154  ----
   155  28.0
   156  
   157  eval
   158  extract(dayofyear from '2010-09-28 12:13:14.1+00:00'::timestamp)
   159  ----
   160  271.0
   161  
   162  eval
   163  extract(week from '2010-01-14 12:13:14.1+00:00'::timestamp)
   164  ----
   165  2.0
   166  
   167  eval
   168  extract(dayofweek from '2010-09-28 12:13:14.1+00:00'::timestamp)
   169  ----
   170  2.0
   171  
   172  eval
   173  extract(quarter from '2010-09-28 12:13:14.1+00:00'::timestamp)
   174  ----
   175  3.0
   176  
   177  eval
   178  extract(hour from '2010-01-10 12:13:14.1+00:00'::timestamp)
   179  ----
   180  12.0
   181  
   182  eval
   183  extract(minute from '2010-01-10 12:13:14.1+00:00'::timestamp)
   184  ----
   185  13.0
   186  
   187  eval
   188  extract(second from '2010-01-10 12:13:14.1+00:00'::timestamp)
   189  ----
   190  14.1
   191  
   192  eval
   193  extract(millisecond from '2010-01-10 12:13:14.123456+00:00'::timestamp)
   194  ----
   195  14123.456
   196  
   197  eval
   198  extract(microsecond from '2010-01-10 12:13:14.123456+00:00'::timestamp)
   199  ----
   200  1.4123456e+07
   201  
   202  eval
   203  extract(epoch from '2010-01-10 12:13:14.1+00:00'::timestamp)
   204  ----
   205  1.2631255941e+09
   206  
   207  eval
   208  extract(century from '2010-01-10 12:13:14.1+00:00'::timestamp)
   209  ----
   210  21.0
   211  
   212  eval
   213  extract(century from '0001-01-10 12:13:14.1+00:00 BC'::timestamp)
   214  ----
   215  -1.0
   216  
   217  eval
   218  extract(decade from '2010-01-10 12:13:14.1+00:00'::timestamp)
   219  ----
   220  201.0
   221  
   222  eval
   223  extract(decade from '2010-01-10 12:13:14.1+00:00 BC'::timestamp)
   224  ----
   225  -201.0
   226  
   227  eval
   228  extract(isoyear from '2006-01-01 12:13:14.1+00:00'::timestamp)
   229  ----
   230  2005.0
   231  
   232  eval
   233  extract(isoyear from '2006-01-02 12:13:14.1+00:00'::timestamp)
   234  ----
   235  2006.0
   236  
   237  eval
   238  extract(millennium from '2010-01-10 12:13:14.1+00:00'::timestamp)
   239  ----
   240  3.0
   241  
   242  eval
   243  extract(millennium from '0010-01-10 12:13:14.1+00:00 BC'::timestamp)
   244  ----
   245  -1.0
   246  
   247  eval
   248  extract(julian from '2010-01-10 12:13:14.1+00:00'::timestamp)
   249  ----
   250  2.4552075091909724e+06
   251  
   252  eval
   253  extract(julian from '4714-11-24 12:13:14.1+00:00 BC'::timestamp)
   254  ----
   255  0.5091909722222222
   256  
   257  # Extract from intervals.
   258  
   259  eval
   260  extract_duration(hour from '123m')
   261  ----
   262  2
   263  
   264  eval
   265  extract_duration('hour' from '123m')
   266  ----
   267  2
   268  
   269  eval
   270  extract_duration('HOUR' from '123m')
   271  ----
   272  2
   273  
   274  eval
   275  extract_duration(hour from '123m'::interval)
   276  ----
   277  2
   278  
   279  eval
   280  extract_duration(minute from '123m10s'::interval)
   281  ----
   282  123
   283  
   284  eval
   285  extract_duration(second from '10m20s30ms'::interval)
   286  ----
   287  620
   288  
   289  eval
   290  extract_duration(millisecond from '20s30ms40µs'::interval)
   291  ----
   292  20030
   293  
   294  eval
   295  extract(hour from '12:00:00+01'::timetz)
   296  ----
   297  12.0
   298  
   299  eval
   300  extract(minute from '12:30:00+01'::timetz)
   301  ----
   302  30.0
   303  
   304  eval
   305  extract(second from '12:00:30+01'::timetz)
   306  ----
   307  30.0
   308  
   309  eval
   310  extract(millisecond from '12:00:00.123456+01'::timetz)
   311  ----
   312  123.456
   313  
   314  eval
   315  extract(microsecond from '12:00:00.123456+01'::timetz)
   316  ----
   317  123456.0