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