1-- 2-- TIMESTAMPTZ 3-- 4CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone); 5-- Test shorthand input values 6-- We can't just "select" the results since they aren't constants; test for 7-- equality instead. We can do that by running the test inside a transaction 8-- block, within which the value of 'now' shouldn't change, and so these 9-- related values shouldn't either. 10BEGIN; 11INSERT INTO TIMESTAMPTZ_TBL VALUES ('today'); 12INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday'); 13INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow'); 14INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow EST'); 15INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu'); 16SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today'; 17 one 18----- 19 1 20(1 row) 21 22SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow'; 23 one 24----- 25 1 26(1 row) 27 28SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday'; 29 one 30----- 31 1 32(1 row) 33 34SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST'; 35 one 36----- 37 1 38(1 row) 39 40SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu'; 41 one 42----- 43 1 44(1 row) 45 46COMMIT; 47DELETE FROM TIMESTAMPTZ_TBL; 48-- Verify that 'now' *does* change over a reasonable interval such as 100 msec, 49-- and that it doesn't change over the same interval within a transaction block 50INSERT INTO TIMESTAMPTZ_TBL VALUES ('now'); 51SELECT pg_sleep(0.1); 52 pg_sleep 53---------- 54 55(1 row) 56 57BEGIN; 58INSERT INTO TIMESTAMPTZ_TBL VALUES ('now'); 59SELECT pg_sleep(0.1); 60 pg_sleep 61---------- 62 63(1 row) 64 65INSERT INTO TIMESTAMPTZ_TBL VALUES ('now'); 66SELECT pg_sleep(0.1); 67 pg_sleep 68---------- 69 70(1 row) 71 72SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now'; 73 two 74----- 75 2 76(1 row) 77 78SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL; 79 three | two 80-------+----- 81 3 | 2 82(1 row) 83 84COMMIT; 85TRUNCATE TIMESTAMPTZ_TBL; 86-- Special values 87INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity'); 88INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity'); 89INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch'); 90-- Obsolete special values 91INSERT INTO TIMESTAMPTZ_TBL VALUES ('invalid'); 92ERROR: date/time value "invalid" is no longer supported 93LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('invalid'); 94 ^ 95INSERT INTO TIMESTAMPTZ_TBL VALUES ('undefined'); 96ERROR: date/time value "undefined" is no longer supported 97LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('undefined'); 98 ^ 99INSERT INTO TIMESTAMPTZ_TBL VALUES ('current'); 100ERROR: date/time value "current" is no longer supported 101LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('current'); 102 ^ 103-- Postgres v6.0 standard output format 104INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST'); 105-- Variations on Postgres v6.1 standard output format 106INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST'); 107INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST'); 108INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST'); 109INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST'); 110INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST'); 111-- ISO 8601 format 112INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02'); 113INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02 03:04:05'); 114INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-08'); 115INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-0800'); 116INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 -08:00'); 117INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800'); 118INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00'); 119INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20'); 120-- POSIX format (note that the timezone abbrev is just decoration here) 121INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); 122INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); 123INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); 124INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8'); 125INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); 126-- Variations for acceptable input formats 127INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); 128INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997'); 129INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 5:32PM 1997'); 130INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997/02/10 17:32:01-0800'); 131INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 PST'); 132INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb-10-1997 17:32:01 PST'); 133INSERT INTO TIMESTAMPTZ_TBL VALUES ('02-10-1997 17:32:01 PST'); 134INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 PST'); 135set datestyle to ymd; 136INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC'); 137INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC'); 138reset datestyle; 139INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC'); 140-- timestamps at different timezones 141INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 America/New_York'); 142SELECT '19970210 173201' AT TIME ZONE 'America/New_York'; 143 timezone 144-------------------------- 145 Mon Feb 10 20:32:01 1997 146(1 row) 147 148INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/New_York'); 149SELECT '19970710 173201' AT TIME ZONE 'America/New_York'; 150 timezone 151-------------------------- 152 Thu Jul 10 20:32:01 1997 153(1 row) 154 155INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/Does_not_exist'); 156ERROR: time zone "america/does_not_exist" not recognized 157LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America... 158 ^ 159SELECT '19970710 173201' AT TIME ZONE 'America/Does_not_exist'; 160ERROR: time zone "America/Does_not_exist" not recognized 161-- Daylight saving time for timestamps beyond 32-bit time_t range. 162SELECT '20500710 173201 Europe/Helsinki'::timestamptz; -- DST 163 timestamptz 164------------------------------ 165 Sun Jul 10 07:32:01 2050 PDT 166(1 row) 167 168SELECT '20500110 173201 Europe/Helsinki'::timestamptz; -- non-DST 169 timestamptz 170------------------------------ 171 Mon Jan 10 07:32:01 2050 PST 172(1 row) 173 174SELECT '205000-07-10 17:32:01 Europe/Helsinki'::timestamptz; -- DST 175 timestamptz 176-------------------------------- 177 Thu Jul 10 07:32:01 205000 PDT 178(1 row) 179 180SELECT '205000-01-10 17:32:01 Europe/Helsinki'::timestamptz; -- non-DST 181 timestamptz 182-------------------------------- 183 Fri Jan 10 07:32:01 205000 PST 184(1 row) 185 186-- Check date conversion and date arithmetic 187INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT'); 188INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997'); 189INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 11 17:32:01 1997'); 190INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 12 17:32:01 1997'); 191INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 13 17:32:01 1997'); 192INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 14 17:32:01 1997'); 193INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 15 17:32:01 1997'); 194INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997'); 195INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097 BC'); 196INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097'); 197INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0597'); 198INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1097'); 199INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1697'); 200INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1797'); 201INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1897'); 202INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997'); 203INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 2097'); 204INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1996'); 205INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1996'); 206INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1996'); 207INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1996'); 208INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1996'); 209INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 1997'); 210INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1997'); 211INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997'); 212ERROR: date/time field value out of range: "Feb 29 17:32:01 1997" 213LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997'); 214 ^ 215INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1997'); 216INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1997'); 217INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1997'); 218INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1999'); 219INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2000'); 220INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2000'); 221INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2001'); 222-- Currently unsupported syntax and ranges 223INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097'); 224ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097" 225LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097')... 226 ^ 227INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC'); 228ERROR: timestamp out of range: "Feb 16 17:32:01 5097 BC" 229LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC... 230 ^ 231-- Alternative field order that we've historically supported (sort of) 232-- with regular and POSIXy timezone specs 233SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz; 234 timestamptz 235------------------------------ 236 Wed Jul 11 07:51:14 2001 PDT 237(1 row) 238 239SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz; 240 timestamptz 241------------------------------ 242 Tue Jul 10 23:51:14 2001 PDT 243(1 row) 244 245SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz; 246 timestamptz 247------------------------------ 248 Wed Jul 11 07:51:14 2001 PDT 249(1 row) 250 251SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz; 252 timestamptz 253------------------------------ 254 Wed Jul 11 00:51:14 2001 PDT 255(1 row) 256 257SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz; 258 timestamptz 259------------------------------ 260 Wed Jul 11 06:51:14 2001 PDT 261(1 row) 262 263SELECT '' AS "64", d1 FROM TIMESTAMPTZ_TBL; 264 64 | d1 265----+--------------------------------- 266 | -infinity 267 | infinity 268 | Wed Dec 31 16:00:00 1969 PST 269 | Mon Feb 10 17:32:01 1997 PST 270 | Mon Feb 10 17:32:01 1997 PST 271 | Mon Feb 10 17:32:02 1997 PST 272 | Mon Feb 10 17:32:01.4 1997 PST 273 | Mon Feb 10 17:32:01.5 1997 PST 274 | Mon Feb 10 17:32:01.6 1997 PST 275 | Thu Jan 02 00:00:00 1997 PST 276 | Thu Jan 02 03:04:05 1997 PST 277 | Mon Feb 10 17:32:01 1997 PST 278 | Mon Feb 10 17:32:01 1997 PST 279 | Mon Feb 10 17:32:01 1997 PST 280 | Mon Feb 10 17:32:01 1997 PST 281 | Tue Jun 10 17:32:01 1997 PDT 282 | Sat Sep 22 18:19:20 2001 PDT 283 | Wed Mar 15 08:14:01 2000 PST 284 | Wed Mar 15 04:14:02 2000 PST 285 | Wed Mar 15 02:14:03 2000 PST 286 | Wed Mar 15 03:14:04 2000 PST 287 | Wed Mar 15 01:14:05 2000 PST 288 | Mon Feb 10 17:32:01 1997 PST 289 | Mon Feb 10 17:32:01 1997 PST 290 | Mon Feb 10 17:32:00 1997 PST 291 | Mon Feb 10 17:32:01 1997 PST 292 | Mon Feb 10 17:32:01 1997 PST 293 | Mon Feb 10 17:32:01 1997 PST 294 | Mon Feb 10 17:32:01 1997 PST 295 | Mon Feb 10 17:32:01 1997 PST 296 | Mon Feb 10 09:32:01 1997 PST 297 | Mon Feb 10 09:32:01 1997 PST 298 | Mon Feb 10 09:32:01 1997 PST 299 | Mon Feb 10 14:32:01 1997 PST 300 | Thu Jul 10 14:32:01 1997 PDT 301 | Tue Jun 10 18:32:01 1997 PDT 302 | Mon Feb 10 17:32:01 1997 PST 303 | Tue Feb 11 17:32:01 1997 PST 304 | Wed Feb 12 17:32:01 1997 PST 305 | Thu Feb 13 17:32:01 1997 PST 306 | Fri Feb 14 17:32:01 1997 PST 307 | Sat Feb 15 17:32:01 1997 PST 308 | Sun Feb 16 17:32:01 1997 PST 309 | Tue Feb 16 17:32:01 0097 PST BC 310 | Sat Feb 16 17:32:01 0097 PST 311 | Thu Feb 16 17:32:01 0597 PST 312 | Tue Feb 16 17:32:01 1097 PST 313 | Sat Feb 16 17:32:01 1697 PST 314 | Thu Feb 16 17:32:01 1797 PST 315 | Tue Feb 16 17:32:01 1897 PST 316 | Sun Feb 16 17:32:01 1997 PST 317 | Sat Feb 16 17:32:01 2097 PST 318 | Wed Feb 28 17:32:01 1996 PST 319 | Thu Feb 29 17:32:01 1996 PST 320 | Fri Mar 01 17:32:01 1996 PST 321 | Mon Dec 30 17:32:01 1996 PST 322 | Tue Dec 31 17:32:01 1996 PST 323 | Wed Jan 01 17:32:01 1997 PST 324 | Fri Feb 28 17:32:01 1997 PST 325 | Sat Mar 01 17:32:01 1997 PST 326 | Tue Dec 30 17:32:01 1997 PST 327 | Wed Dec 31 17:32:01 1997 PST 328 | Fri Dec 31 17:32:01 1999 PST 329 | Sat Jan 01 17:32:01 2000 PST 330 | Sun Dec 31 17:32:01 2000 PST 331 | Mon Jan 01 17:32:01 2001 PST 332(66 rows) 333 334-- Check behavior at the lower boundary of the timestamp range 335SELECT '4714-11-24 00:00:00+00 BC'::timestamptz; 336 timestamptz 337--------------------------------- 338 Sun Nov 23 16:00:00 4714 PST BC 339(1 row) 340 341SELECT '4714-11-23 16:00:00-08 BC'::timestamptz; 342 timestamptz 343--------------------------------- 344 Sun Nov 23 16:00:00 4714 PST BC 345(1 row) 346 347SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz; 348 timestamptz 349--------------------------------- 350 Sun Nov 23 16:00:00 4714 PST BC 351(1 row) 352 353SELECT '4714-11-23 23:59:59+00 BC'::timestamptz; -- out of range 354ERROR: timestamp out of range: "4714-11-23 23:59:59+00 BC" 355LINE 1: SELECT '4714-11-23 23:59:59+00 BC'::timestamptz; 356 ^ 357-- The upper boundary differs between integer and float timestamps, so no check 358-- Demonstrate functions and operators 359SELECT '' AS "48", d1 FROM TIMESTAMPTZ_TBL 360 WHERE d1 > timestamp with time zone '1997-01-02'; 361 48 | d1 362----+-------------------------------- 363 | infinity 364 | Mon Feb 10 17:32:01 1997 PST 365 | Mon Feb 10 17:32:01 1997 PST 366 | Mon Feb 10 17:32:02 1997 PST 367 | Mon Feb 10 17:32:01.4 1997 PST 368 | Mon Feb 10 17:32:01.5 1997 PST 369 | Mon Feb 10 17:32:01.6 1997 PST 370 | Thu Jan 02 03:04:05 1997 PST 371 | Mon Feb 10 17:32:01 1997 PST 372 | Mon Feb 10 17:32:01 1997 PST 373 | Mon Feb 10 17:32:01 1997 PST 374 | Mon Feb 10 17:32:01 1997 PST 375 | Tue Jun 10 17:32:01 1997 PDT 376 | Sat Sep 22 18:19:20 2001 PDT 377 | Wed Mar 15 08:14:01 2000 PST 378 | Wed Mar 15 04:14:02 2000 PST 379 | Wed Mar 15 02:14:03 2000 PST 380 | Wed Mar 15 03:14:04 2000 PST 381 | Wed Mar 15 01:14:05 2000 PST 382 | Mon Feb 10 17:32:01 1997 PST 383 | Mon Feb 10 17:32:01 1997 PST 384 | Mon Feb 10 17:32:00 1997 PST 385 | Mon Feb 10 17:32:01 1997 PST 386 | Mon Feb 10 17:32:01 1997 PST 387 | Mon Feb 10 17:32:01 1997 PST 388 | Mon Feb 10 17:32:01 1997 PST 389 | Mon Feb 10 17:32:01 1997 PST 390 | Mon Feb 10 09:32:01 1997 PST 391 | Mon Feb 10 09:32:01 1997 PST 392 | Mon Feb 10 09:32:01 1997 PST 393 | Mon Feb 10 14:32:01 1997 PST 394 | Thu Jul 10 14:32:01 1997 PDT 395 | Tue Jun 10 18:32:01 1997 PDT 396 | Mon Feb 10 17:32:01 1997 PST 397 | Tue Feb 11 17:32:01 1997 PST 398 | Wed Feb 12 17:32:01 1997 PST 399 | Thu Feb 13 17:32:01 1997 PST 400 | Fri Feb 14 17:32:01 1997 PST 401 | Sat Feb 15 17:32:01 1997 PST 402 | Sun Feb 16 17:32:01 1997 PST 403 | Sun Feb 16 17:32:01 1997 PST 404 | Sat Feb 16 17:32:01 2097 PST 405 | Fri Feb 28 17:32:01 1997 PST 406 | Sat Mar 01 17:32:01 1997 PST 407 | Tue Dec 30 17:32:01 1997 PST 408 | Wed Dec 31 17:32:01 1997 PST 409 | Fri Dec 31 17:32:01 1999 PST 410 | Sat Jan 01 17:32:01 2000 PST 411 | Sun Dec 31 17:32:01 2000 PST 412 | Mon Jan 01 17:32:01 2001 PST 413(50 rows) 414 415SELECT '' AS "15", d1 FROM TIMESTAMPTZ_TBL 416 WHERE d1 < timestamp with time zone '1997-01-02'; 417 15 | d1 418----+--------------------------------- 419 | -infinity 420 | Wed Dec 31 16:00:00 1969 PST 421 | Tue Feb 16 17:32:01 0097 PST BC 422 | Sat Feb 16 17:32:01 0097 PST 423 | Thu Feb 16 17:32:01 0597 PST 424 | Tue Feb 16 17:32:01 1097 PST 425 | Sat Feb 16 17:32:01 1697 PST 426 | Thu Feb 16 17:32:01 1797 PST 427 | Tue Feb 16 17:32:01 1897 PST 428 | Wed Feb 28 17:32:01 1996 PST 429 | Thu Feb 29 17:32:01 1996 PST 430 | Fri Mar 01 17:32:01 1996 PST 431 | Mon Dec 30 17:32:01 1996 PST 432 | Tue Dec 31 17:32:01 1996 PST 433 | Wed Jan 01 17:32:01 1997 PST 434(15 rows) 435 436SELECT '' AS one, d1 FROM TIMESTAMPTZ_TBL 437 WHERE d1 = timestamp with time zone '1997-01-02'; 438 one | d1 439-----+------------------------------ 440 | Thu Jan 02 00:00:00 1997 PST 441(1 row) 442 443SELECT '' AS "63", d1 FROM TIMESTAMPTZ_TBL 444 WHERE d1 != timestamp with time zone '1997-01-02'; 445 63 | d1 446----+--------------------------------- 447 | -infinity 448 | infinity 449 | Wed Dec 31 16:00:00 1969 PST 450 | Mon Feb 10 17:32:01 1997 PST 451 | Mon Feb 10 17:32:01 1997 PST 452 | Mon Feb 10 17:32:02 1997 PST 453 | Mon Feb 10 17:32:01.4 1997 PST 454 | Mon Feb 10 17:32:01.5 1997 PST 455 | Mon Feb 10 17:32:01.6 1997 PST 456 | Thu Jan 02 03:04:05 1997 PST 457 | Mon Feb 10 17:32:01 1997 PST 458 | Mon Feb 10 17:32:01 1997 PST 459 | Mon Feb 10 17:32:01 1997 PST 460 | Mon Feb 10 17:32:01 1997 PST 461 | Tue Jun 10 17:32:01 1997 PDT 462 | Sat Sep 22 18:19:20 2001 PDT 463 | Wed Mar 15 08:14:01 2000 PST 464 | Wed Mar 15 04:14:02 2000 PST 465 | Wed Mar 15 02:14:03 2000 PST 466 | Wed Mar 15 03:14:04 2000 PST 467 | Wed Mar 15 01:14:05 2000 PST 468 | Mon Feb 10 17:32:01 1997 PST 469 | Mon Feb 10 17:32:01 1997 PST 470 | Mon Feb 10 17:32:00 1997 PST 471 | Mon Feb 10 17:32:01 1997 PST 472 | Mon Feb 10 17:32:01 1997 PST 473 | Mon Feb 10 17:32:01 1997 PST 474 | Mon Feb 10 17:32:01 1997 PST 475 | Mon Feb 10 17:32:01 1997 PST 476 | Mon Feb 10 09:32:01 1997 PST 477 | Mon Feb 10 09:32:01 1997 PST 478 | Mon Feb 10 09:32:01 1997 PST 479 | Mon Feb 10 14:32:01 1997 PST 480 | Thu Jul 10 14:32:01 1997 PDT 481 | Tue Jun 10 18:32:01 1997 PDT 482 | Mon Feb 10 17:32:01 1997 PST 483 | Tue Feb 11 17:32:01 1997 PST 484 | Wed Feb 12 17:32:01 1997 PST 485 | Thu Feb 13 17:32:01 1997 PST 486 | Fri Feb 14 17:32:01 1997 PST 487 | Sat Feb 15 17:32:01 1997 PST 488 | Sun Feb 16 17:32:01 1997 PST 489 | Tue Feb 16 17:32:01 0097 PST BC 490 | Sat Feb 16 17:32:01 0097 PST 491 | Thu Feb 16 17:32:01 0597 PST 492 | Tue Feb 16 17:32:01 1097 PST 493 | Sat Feb 16 17:32:01 1697 PST 494 | Thu Feb 16 17:32:01 1797 PST 495 | Tue Feb 16 17:32:01 1897 PST 496 | Sun Feb 16 17:32:01 1997 PST 497 | Sat Feb 16 17:32:01 2097 PST 498 | Wed Feb 28 17:32:01 1996 PST 499 | Thu Feb 29 17:32:01 1996 PST 500 | Fri Mar 01 17:32:01 1996 PST 501 | Mon Dec 30 17:32:01 1996 PST 502 | Tue Dec 31 17:32:01 1996 PST 503 | Wed Jan 01 17:32:01 1997 PST 504 | Fri Feb 28 17:32:01 1997 PST 505 | Sat Mar 01 17:32:01 1997 PST 506 | Tue Dec 30 17:32:01 1997 PST 507 | Wed Dec 31 17:32:01 1997 PST 508 | Fri Dec 31 17:32:01 1999 PST 509 | Sat Jan 01 17:32:01 2000 PST 510 | Sun Dec 31 17:32:01 2000 PST 511 | Mon Jan 01 17:32:01 2001 PST 512(65 rows) 513 514SELECT '' AS "16", d1 FROM TIMESTAMPTZ_TBL 515 WHERE d1 <= timestamp with time zone '1997-01-02'; 516 16 | d1 517----+--------------------------------- 518 | -infinity 519 | Wed Dec 31 16:00:00 1969 PST 520 | Thu Jan 02 00:00:00 1997 PST 521 | Tue Feb 16 17:32:01 0097 PST BC 522 | Sat Feb 16 17:32:01 0097 PST 523 | Thu Feb 16 17:32:01 0597 PST 524 | Tue Feb 16 17:32:01 1097 PST 525 | Sat Feb 16 17:32:01 1697 PST 526 | Thu Feb 16 17:32:01 1797 PST 527 | Tue Feb 16 17:32:01 1897 PST 528 | Wed Feb 28 17:32:01 1996 PST 529 | Thu Feb 29 17:32:01 1996 PST 530 | Fri Mar 01 17:32:01 1996 PST 531 | Mon Dec 30 17:32:01 1996 PST 532 | Tue Dec 31 17:32:01 1996 PST 533 | Wed Jan 01 17:32:01 1997 PST 534(16 rows) 535 536SELECT '' AS "49", d1 FROM TIMESTAMPTZ_TBL 537 WHERE d1 >= timestamp with time zone '1997-01-02'; 538 49 | d1 539----+-------------------------------- 540 | infinity 541 | Mon Feb 10 17:32:01 1997 PST 542 | Mon Feb 10 17:32:01 1997 PST 543 | Mon Feb 10 17:32:02 1997 PST 544 | Mon Feb 10 17:32:01.4 1997 PST 545 | Mon Feb 10 17:32:01.5 1997 PST 546 | Mon Feb 10 17:32:01.6 1997 PST 547 | Thu Jan 02 00:00:00 1997 PST 548 | Thu Jan 02 03:04:05 1997 PST 549 | Mon Feb 10 17:32:01 1997 PST 550 | Mon Feb 10 17:32:01 1997 PST 551 | Mon Feb 10 17:32:01 1997 PST 552 | Mon Feb 10 17:32:01 1997 PST 553 | Tue Jun 10 17:32:01 1997 PDT 554 | Sat Sep 22 18:19:20 2001 PDT 555 | Wed Mar 15 08:14:01 2000 PST 556 | Wed Mar 15 04:14:02 2000 PST 557 | Wed Mar 15 02:14:03 2000 PST 558 | Wed Mar 15 03:14:04 2000 PST 559 | Wed Mar 15 01:14:05 2000 PST 560 | Mon Feb 10 17:32:01 1997 PST 561 | Mon Feb 10 17:32:01 1997 PST 562 | Mon Feb 10 17:32:00 1997 PST 563 | Mon Feb 10 17:32:01 1997 PST 564 | Mon Feb 10 17:32:01 1997 PST 565 | Mon Feb 10 17:32:01 1997 PST 566 | Mon Feb 10 17:32:01 1997 PST 567 | Mon Feb 10 17:32:01 1997 PST 568 | Mon Feb 10 09:32:01 1997 PST 569 | Mon Feb 10 09:32:01 1997 PST 570 | Mon Feb 10 09:32:01 1997 PST 571 | Mon Feb 10 14:32:01 1997 PST 572 | Thu Jul 10 14:32:01 1997 PDT 573 | Tue Jun 10 18:32:01 1997 PDT 574 | Mon Feb 10 17:32:01 1997 PST 575 | Tue Feb 11 17:32:01 1997 PST 576 | Wed Feb 12 17:32:01 1997 PST 577 | Thu Feb 13 17:32:01 1997 PST 578 | Fri Feb 14 17:32:01 1997 PST 579 | Sat Feb 15 17:32:01 1997 PST 580 | Sun Feb 16 17:32:01 1997 PST 581 | Sun Feb 16 17:32:01 1997 PST 582 | Sat Feb 16 17:32:01 2097 PST 583 | Fri Feb 28 17:32:01 1997 PST 584 | Sat Mar 01 17:32:01 1997 PST 585 | Tue Dec 30 17:32:01 1997 PST 586 | Wed Dec 31 17:32:01 1997 PST 587 | Fri Dec 31 17:32:01 1999 PST 588 | Sat Jan 01 17:32:01 2000 PST 589 | Sun Dec 31 17:32:01 2000 PST 590 | Mon Jan 01 17:32:01 2001 PST 591(51 rows) 592 593SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff 594 FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 595 54 | diff 596----+---------------------------------------- 597 | @ 9863 days 8 hours ago 598 | @ 39 days 17 hours 32 mins 1 sec 599 | @ 39 days 17 hours 32 mins 1 sec 600 | @ 39 days 17 hours 32 mins 2 secs 601 | @ 39 days 17 hours 32 mins 1.4 secs 602 | @ 39 days 17 hours 32 mins 1.5 secs 603 | @ 39 days 17 hours 32 mins 1.6 secs 604 | @ 0 605 | @ 3 hours 4 mins 5 secs 606 | @ 39 days 17 hours 32 mins 1 sec 607 | @ 39 days 17 hours 32 mins 1 sec 608 | @ 39 days 17 hours 32 mins 1 sec 609 | @ 39 days 17 hours 32 mins 1 sec 610 | @ 159 days 16 hours 32 mins 1 sec 611 | @ 1724 days 17 hours 19 mins 20 secs 612 | @ 1168 days 8 hours 14 mins 1 sec 613 | @ 1168 days 4 hours 14 mins 2 secs 614 | @ 1168 days 2 hours 14 mins 3 secs 615 | @ 1168 days 3 hours 14 mins 4 secs 616 | @ 1168 days 1 hour 14 mins 5 secs 617 | @ 39 days 17 hours 32 mins 1 sec 618 | @ 39 days 17 hours 32 mins 1 sec 619 | @ 39 days 17 hours 32 mins 620 | @ 39 days 17 hours 32 mins 1 sec 621 | @ 39 days 17 hours 32 mins 1 sec 622 | @ 39 days 17 hours 32 mins 1 sec 623 | @ 39 days 17 hours 32 mins 1 sec 624 | @ 39 days 17 hours 32 mins 1 sec 625 | @ 39 days 9 hours 32 mins 1 sec 626 | @ 39 days 9 hours 32 mins 1 sec 627 | @ 39 days 9 hours 32 mins 1 sec 628 | @ 39 days 14 hours 32 mins 1 sec 629 | @ 189 days 13 hours 32 mins 1 sec 630 | @ 159 days 17 hours 32 mins 1 sec 631 | @ 39 days 17 hours 32 mins 1 sec 632 | @ 40 days 17 hours 32 mins 1 sec 633 | @ 41 days 17 hours 32 mins 1 sec 634 | @ 42 days 17 hours 32 mins 1 sec 635 | @ 43 days 17 hours 32 mins 1 sec 636 | @ 44 days 17 hours 32 mins 1 sec 637 | @ 45 days 17 hours 32 mins 1 sec 638 | @ 45 days 17 hours 32 mins 1 sec 639 | @ 308 days 6 hours 27 mins 59 secs ago 640 | @ 307 days 6 hours 27 mins 59 secs ago 641 | @ 306 days 6 hours 27 mins 59 secs ago 642 | @ 2 days 6 hours 27 mins 59 secs ago 643 | @ 1 day 6 hours 27 mins 59 secs ago 644 | @ 6 hours 27 mins 59 secs ago 645 | @ 57 days 17 hours 32 mins 1 sec 646 | @ 58 days 17 hours 32 mins 1 sec 647 | @ 362 days 17 hours 32 mins 1 sec 648 | @ 363 days 17 hours 32 mins 1 sec 649 | @ 1093 days 17 hours 32 mins 1 sec 650 | @ 1094 days 17 hours 32 mins 1 sec 651 | @ 1459 days 17 hours 32 mins 1 sec 652 | @ 1460 days 17 hours 32 mins 1 sec 653(56 rows) 654 655SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc; 656 date_trunc_week | week_trunc 657-----------------+------------------------------ 658 | Mon Feb 23 00:00:00 2004 PST 659(1 row) 660 661-- Test casting within a BETWEEN qualifier 662SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff 663 FROM TIMESTAMPTZ_TBL 664 WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01'; 665 54 | diff 666----+---------------------------------------- 667 | @ 9863 days 8 hours ago 668 | @ 39 days 17 hours 32 mins 1 sec 669 | @ 39 days 17 hours 32 mins 1 sec 670 | @ 39 days 17 hours 32 mins 2 secs 671 | @ 39 days 17 hours 32 mins 1.4 secs 672 | @ 39 days 17 hours 32 mins 1.5 secs 673 | @ 39 days 17 hours 32 mins 1.6 secs 674 | @ 0 675 | @ 3 hours 4 mins 5 secs 676 | @ 39 days 17 hours 32 mins 1 sec 677 | @ 39 days 17 hours 32 mins 1 sec 678 | @ 39 days 17 hours 32 mins 1 sec 679 | @ 39 days 17 hours 32 mins 1 sec 680 | @ 159 days 16 hours 32 mins 1 sec 681 | @ 1724 days 17 hours 19 mins 20 secs 682 | @ 1168 days 8 hours 14 mins 1 sec 683 | @ 1168 days 4 hours 14 mins 2 secs 684 | @ 1168 days 2 hours 14 mins 3 secs 685 | @ 1168 days 3 hours 14 mins 4 secs 686 | @ 1168 days 1 hour 14 mins 5 secs 687 | @ 39 days 17 hours 32 mins 1 sec 688 | @ 39 days 17 hours 32 mins 1 sec 689 | @ 39 days 17 hours 32 mins 690 | @ 39 days 17 hours 32 mins 1 sec 691 | @ 39 days 17 hours 32 mins 1 sec 692 | @ 39 days 17 hours 32 mins 1 sec 693 | @ 39 days 17 hours 32 mins 1 sec 694 | @ 39 days 17 hours 32 mins 1 sec 695 | @ 39 days 9 hours 32 mins 1 sec 696 | @ 39 days 9 hours 32 mins 1 sec 697 | @ 39 days 9 hours 32 mins 1 sec 698 | @ 39 days 14 hours 32 mins 1 sec 699 | @ 189 days 13 hours 32 mins 1 sec 700 | @ 159 days 17 hours 32 mins 1 sec 701 | @ 39 days 17 hours 32 mins 1 sec 702 | @ 40 days 17 hours 32 mins 1 sec 703 | @ 41 days 17 hours 32 mins 1 sec 704 | @ 42 days 17 hours 32 mins 1 sec 705 | @ 43 days 17 hours 32 mins 1 sec 706 | @ 44 days 17 hours 32 mins 1 sec 707 | @ 45 days 17 hours 32 mins 1 sec 708 | @ 45 days 17 hours 32 mins 1 sec 709 | @ 308 days 6 hours 27 mins 59 secs ago 710 | @ 307 days 6 hours 27 mins 59 secs ago 711 | @ 306 days 6 hours 27 mins 59 secs ago 712 | @ 2 days 6 hours 27 mins 59 secs ago 713 | @ 1 day 6 hours 27 mins 59 secs ago 714 | @ 6 hours 27 mins 59 secs ago 715 | @ 57 days 17 hours 32 mins 1 sec 716 | @ 58 days 17 hours 32 mins 1 sec 717 | @ 362 days 17 hours 32 mins 1 sec 718 | @ 363 days 17 hours 32 mins 1 sec 719 | @ 1093 days 17 hours 32 mins 1 sec 720 | @ 1094 days 17 hours 32 mins 1 sec 721 | @ 1459 days 17 hours 32 mins 1 sec 722 | @ 1460 days 17 hours 32 mins 1 sec 723(56 rows) 724 725-- DATE_PART (timestamptz_part) 726SELECT d1 as timestamptz, 727 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month, 728 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour, 729 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second 730 FROM TIMESTAMPTZ_TBL; 731 timestamptz | year | month | day | hour | minute | second 732---------------------------------+-----------+-------+-----+------+--------+-------- 733 -infinity | -Infinity | | | | | 734 infinity | Infinity | | | | | 735 Wed Dec 31 16:00:00 1969 PST | 1969 | 12 | 31 | 16 | 0 | 0 736 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 737 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 738 Mon Feb 10 17:32:02 1997 PST | 1997 | 2 | 10 | 17 | 32 | 2 739 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.4 740 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.5 741 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.6 742 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 2 | 0 | 0 | 0 743 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 2 | 3 | 4 | 5 744 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 745 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 746 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 747 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 748 Tue Jun 10 17:32:01 1997 PDT | 1997 | 6 | 10 | 17 | 32 | 1 749 Sat Sep 22 18:19:20 2001 PDT | 2001 | 9 | 22 | 18 | 19 | 20 750 Wed Mar 15 08:14:01 2000 PST | 2000 | 3 | 15 | 8 | 14 | 1 751 Wed Mar 15 04:14:02 2000 PST | 2000 | 3 | 15 | 4 | 14 | 2 752 Wed Mar 15 02:14:03 2000 PST | 2000 | 3 | 15 | 2 | 14 | 3 753 Wed Mar 15 03:14:04 2000 PST | 2000 | 3 | 15 | 3 | 14 | 4 754 Wed Mar 15 01:14:05 2000 PST | 2000 | 3 | 15 | 1 | 14 | 5 755 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 756 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 757 Mon Feb 10 17:32:00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 0 758 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 759 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 760 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 761 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 762 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 763 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1 764 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1 765 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1 766 Mon Feb 10 14:32:01 1997 PST | 1997 | 2 | 10 | 14 | 32 | 1 767 Thu Jul 10 14:32:01 1997 PDT | 1997 | 7 | 10 | 14 | 32 | 1 768 Tue Jun 10 18:32:01 1997 PDT | 1997 | 6 | 10 | 18 | 32 | 1 769 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 770 Tue Feb 11 17:32:01 1997 PST | 1997 | 2 | 11 | 17 | 32 | 1 771 Wed Feb 12 17:32:01 1997 PST | 1997 | 2 | 12 | 17 | 32 | 1 772 Thu Feb 13 17:32:01 1997 PST | 1997 | 2 | 13 | 17 | 32 | 1 773 Fri Feb 14 17:32:01 1997 PST | 1997 | 2 | 14 | 17 | 32 | 1 774 Sat Feb 15 17:32:01 1997 PST | 1997 | 2 | 15 | 17 | 32 | 1 775 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1 776 Tue Feb 16 17:32:01 0097 PST BC | -97 | 2 | 16 | 17 | 32 | 1 777 Sat Feb 16 17:32:01 0097 PST | 97 | 2 | 16 | 17 | 32 | 1 778 Thu Feb 16 17:32:01 0597 PST | 597 | 2 | 16 | 17 | 32 | 1 779 Tue Feb 16 17:32:01 1097 PST | 1097 | 2 | 16 | 17 | 32 | 1 780 Sat Feb 16 17:32:01 1697 PST | 1697 | 2 | 16 | 17 | 32 | 1 781 Thu Feb 16 17:32:01 1797 PST | 1797 | 2 | 16 | 17 | 32 | 1 782 Tue Feb 16 17:32:01 1897 PST | 1897 | 2 | 16 | 17 | 32 | 1 783 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1 784 Sat Feb 16 17:32:01 2097 PST | 2097 | 2 | 16 | 17 | 32 | 1 785 Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1 786 Thu Feb 29 17:32:01 1996 PST | 1996 | 2 | 29 | 17 | 32 | 1 787 Fri Mar 01 17:32:01 1996 PST | 1996 | 3 | 1 | 17 | 32 | 1 788 Mon Dec 30 17:32:01 1996 PST | 1996 | 12 | 30 | 17 | 32 | 1 789 Tue Dec 31 17:32:01 1996 PST | 1996 | 12 | 31 | 17 | 32 | 1 790 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 1 | 17 | 32 | 1 791 Fri Feb 28 17:32:01 1997 PST | 1997 | 2 | 28 | 17 | 32 | 1 792 Sat Mar 01 17:32:01 1997 PST | 1997 | 3 | 1 | 17 | 32 | 1 793 Tue Dec 30 17:32:01 1997 PST | 1997 | 12 | 30 | 17 | 32 | 1 794 Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1 795 Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1 796 Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1 797 Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1 798 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1 799(66 rows) 800 801SELECT d1 as timestamptz, 802 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec, 803 date_part( 'usec', d1) AS usec 804 FROM TIMESTAMPTZ_TBL; 805 timestamptz | quarter | msec | usec 806---------------------------------+---------+-------+---------- 807 -infinity | | | 808 infinity | | | 809 Wed Dec 31 16:00:00 1969 PST | 4 | 0 | 0 810 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 811 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 812 Mon Feb 10 17:32:02 1997 PST | 1 | 2000 | 2000000 813 Mon Feb 10 17:32:01.4 1997 PST | 1 | 1400 | 1400000 814 Mon Feb 10 17:32:01.5 1997 PST | 1 | 1500 | 1500000 815 Mon Feb 10 17:32:01.6 1997 PST | 1 | 1600 | 1600000 816 Thu Jan 02 00:00:00 1997 PST | 1 | 0 | 0 817 Thu Jan 02 03:04:05 1997 PST | 1 | 5000 | 5000000 818 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 819 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 820 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 821 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 822 Tue Jun 10 17:32:01 1997 PDT | 2 | 1000 | 1000000 823 Sat Sep 22 18:19:20 2001 PDT | 3 | 20000 | 20000000 824 Wed Mar 15 08:14:01 2000 PST | 1 | 1000 | 1000000 825 Wed Mar 15 04:14:02 2000 PST | 1 | 2000 | 2000000 826 Wed Mar 15 02:14:03 2000 PST | 1 | 3000 | 3000000 827 Wed Mar 15 03:14:04 2000 PST | 1 | 4000 | 4000000 828 Wed Mar 15 01:14:05 2000 PST | 1 | 5000 | 5000000 829 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 830 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 831 Mon Feb 10 17:32:00 1997 PST | 1 | 0 | 0 832 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 833 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 834 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 835 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 836 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 837 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000 838 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000 839 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000 840 Mon Feb 10 14:32:01 1997 PST | 1 | 1000 | 1000000 841 Thu Jul 10 14:32:01 1997 PDT | 3 | 1000 | 1000000 842 Tue Jun 10 18:32:01 1997 PDT | 2 | 1000 | 1000000 843 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000 844 Tue Feb 11 17:32:01 1997 PST | 1 | 1000 | 1000000 845 Wed Feb 12 17:32:01 1997 PST | 1 | 1000 | 1000000 846 Thu Feb 13 17:32:01 1997 PST | 1 | 1000 | 1000000 847 Fri Feb 14 17:32:01 1997 PST | 1 | 1000 | 1000000 848 Sat Feb 15 17:32:01 1997 PST | 1 | 1000 | 1000000 849 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000 850 Tue Feb 16 17:32:01 0097 PST BC | 1 | 1000 | 1000000 851 Sat Feb 16 17:32:01 0097 PST | 1 | 1000 | 1000000 852 Thu Feb 16 17:32:01 0597 PST | 1 | 1000 | 1000000 853 Tue Feb 16 17:32:01 1097 PST | 1 | 1000 | 1000000 854 Sat Feb 16 17:32:01 1697 PST | 1 | 1000 | 1000000 855 Thu Feb 16 17:32:01 1797 PST | 1 | 1000 | 1000000 856 Tue Feb 16 17:32:01 1897 PST | 1 | 1000 | 1000000 857 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000 858 Sat Feb 16 17:32:01 2097 PST | 1 | 1000 | 1000000 859 Wed Feb 28 17:32:01 1996 PST | 1 | 1000 | 1000000 860 Thu Feb 29 17:32:01 1996 PST | 1 | 1000 | 1000000 861 Fri Mar 01 17:32:01 1996 PST | 1 | 1000 | 1000000 862 Mon Dec 30 17:32:01 1996 PST | 4 | 1000 | 1000000 863 Tue Dec 31 17:32:01 1996 PST | 4 | 1000 | 1000000 864 Wed Jan 01 17:32:01 1997 PST | 1 | 1000 | 1000000 865 Fri Feb 28 17:32:01 1997 PST | 1 | 1000 | 1000000 866 Sat Mar 01 17:32:01 1997 PST | 1 | 1000 | 1000000 867 Tue Dec 30 17:32:01 1997 PST | 4 | 1000 | 1000000 868 Wed Dec 31 17:32:01 1997 PST | 4 | 1000 | 1000000 869 Fri Dec 31 17:32:01 1999 PST | 4 | 1000 | 1000000 870 Sat Jan 01 17:32:01 2000 PST | 1 | 1000 | 1000000 871 Sun Dec 31 17:32:01 2000 PST | 4 | 1000 | 1000000 872 Mon Jan 01 17:32:01 2001 PST | 1 | 1000 | 1000000 873(66 rows) 874 875SELECT d1 as timestamptz, 876 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week, 877 date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow, 878 date_part( 'doy', d1) AS doy 879 FROM TIMESTAMPTZ_TBL; 880 timestamptz | isoyear | week | isodow | dow | doy 881---------------------------------+-----------+------+--------+-----+----- 882 -infinity | -Infinity | | | | 883 infinity | Infinity | | | | 884 Wed Dec 31 16:00:00 1969 PST | 1970 | 1 | 3 | 3 | 365 885 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 886 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 887 Mon Feb 10 17:32:02 1997 PST | 1997 | 7 | 1 | 1 | 41 888 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 7 | 1 | 1 | 41 889 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 7 | 1 | 1 | 41 890 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 7 | 1 | 1 | 41 891 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 4 | 4 | 2 892 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 4 | 4 | 2 893 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 894 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 895 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 896 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 897 Tue Jun 10 17:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161 898 Sat Sep 22 18:19:20 2001 PDT | 2001 | 38 | 6 | 6 | 265 899 Wed Mar 15 08:14:01 2000 PST | 2000 | 11 | 3 | 3 | 75 900 Wed Mar 15 04:14:02 2000 PST | 2000 | 11 | 3 | 3 | 75 901 Wed Mar 15 02:14:03 2000 PST | 2000 | 11 | 3 | 3 | 75 902 Wed Mar 15 03:14:04 2000 PST | 2000 | 11 | 3 | 3 | 75 903 Wed Mar 15 01:14:05 2000 PST | 2000 | 11 | 3 | 3 | 75 904 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 905 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 906 Mon Feb 10 17:32:00 1997 PST | 1997 | 7 | 1 | 1 | 41 907 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 908 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 909 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 910 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 911 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 912 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 913 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 914 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 915 Mon Feb 10 14:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 916 Thu Jul 10 14:32:01 1997 PDT | 1997 | 28 | 4 | 4 | 191 917 Tue Jun 10 18:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161 918 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41 919 Tue Feb 11 17:32:01 1997 PST | 1997 | 7 | 2 | 2 | 42 920 Wed Feb 12 17:32:01 1997 PST | 1997 | 7 | 3 | 3 | 43 921 Thu Feb 13 17:32:01 1997 PST | 1997 | 7 | 4 | 4 | 44 922 Fri Feb 14 17:32:01 1997 PST | 1997 | 7 | 5 | 5 | 45 923 Sat Feb 15 17:32:01 1997 PST | 1997 | 7 | 6 | 6 | 46 924 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47 925 Tue Feb 16 17:32:01 0097 PST BC | -97 | 7 | 2 | 2 | 47 926 Sat Feb 16 17:32:01 0097 PST | 97 | 7 | 6 | 6 | 47 927 Thu Feb 16 17:32:01 0597 PST | 597 | 7 | 4 | 4 | 47 928 Tue Feb 16 17:32:01 1097 PST | 1097 | 7 | 2 | 2 | 47 929 Sat Feb 16 17:32:01 1697 PST | 1697 | 7 | 6 | 6 | 47 930 Thu Feb 16 17:32:01 1797 PST | 1797 | 7 | 4 | 4 | 47 931 Tue Feb 16 17:32:01 1897 PST | 1897 | 7 | 2 | 2 | 47 932 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47 933 Sat Feb 16 17:32:01 2097 PST | 2097 | 7 | 6 | 6 | 47 934 Wed Feb 28 17:32:01 1996 PST | 1996 | 9 | 3 | 3 | 59 935 Thu Feb 29 17:32:01 1996 PST | 1996 | 9 | 4 | 4 | 60 936 Fri Mar 01 17:32:01 1996 PST | 1996 | 9 | 5 | 5 | 61 937 Mon Dec 30 17:32:01 1996 PST | 1997 | 1 | 1 | 1 | 365 938 Tue Dec 31 17:32:01 1996 PST | 1997 | 1 | 2 | 2 | 366 939 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 3 | 3 | 1 940 Fri Feb 28 17:32:01 1997 PST | 1997 | 9 | 5 | 5 | 59 941 Sat Mar 01 17:32:01 1997 PST | 1997 | 9 | 6 | 6 | 60 942 Tue Dec 30 17:32:01 1997 PST | 1998 | 1 | 2 | 2 | 364 943 Wed Dec 31 17:32:01 1997 PST | 1998 | 1 | 3 | 3 | 365 944 Fri Dec 31 17:32:01 1999 PST | 1999 | 52 | 5 | 5 | 365 945 Sat Jan 01 17:32:01 2000 PST | 1999 | 52 | 6 | 6 | 1 946 Sun Dec 31 17:32:01 2000 PST | 2000 | 52 | 7 | 0 | 366 947 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 1 | 1 948(66 rows) 949 950SELECT d1 as timestamptz, 951 date_part( 'decade', d1) AS decade, 952 date_part( 'century', d1) AS century, 953 date_part( 'millennium', d1) AS millennium, 954 round(date_part( 'julian', d1)) AS julian 955 FROM TIMESTAMPTZ_TBL; 956 timestamptz | decade | century | millennium | julian 957---------------------------------+-----------+-----------+------------+----------- 958 -infinity | -Infinity | -Infinity | -Infinity | -Infinity 959 infinity | Infinity | Infinity | Infinity | Infinity 960 Wed Dec 31 16:00:00 1969 PST | 196 | 20 | 2 | 2440588 961 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 962 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 963 Mon Feb 10 17:32:02 1997 PST | 199 | 20 | 2 | 2450491 964 Mon Feb 10 17:32:01.4 1997 PST | 199 | 20 | 2 | 2450491 965 Mon Feb 10 17:32:01.5 1997 PST | 199 | 20 | 2 | 2450491 966 Mon Feb 10 17:32:01.6 1997 PST | 199 | 20 | 2 | 2450491 967 Thu Jan 02 00:00:00 1997 PST | 199 | 20 | 2 | 2450451 968 Thu Jan 02 03:04:05 1997 PST | 199 | 20 | 2 | 2450451 969 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 970 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 971 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 972 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 973 Tue Jun 10 17:32:01 1997 PDT | 199 | 20 | 2 | 2450611 974 Sat Sep 22 18:19:20 2001 PDT | 200 | 21 | 3 | 2452176 975 Wed Mar 15 08:14:01 2000 PST | 200 | 20 | 2 | 2451619 976 Wed Mar 15 04:14:02 2000 PST | 200 | 20 | 2 | 2451619 977 Wed Mar 15 02:14:03 2000 PST | 200 | 20 | 2 | 2451619 978 Wed Mar 15 03:14:04 2000 PST | 200 | 20 | 2 | 2451619 979 Wed Mar 15 01:14:05 2000 PST | 200 | 20 | 2 | 2451619 980 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 981 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 982 Mon Feb 10 17:32:00 1997 PST | 199 | 20 | 2 | 2450491 983 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 984 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 985 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 986 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 987 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 988 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 989 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 990 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 991 Mon Feb 10 14:32:01 1997 PST | 199 | 20 | 2 | 2450491 992 Thu Jul 10 14:32:01 1997 PDT | 199 | 20 | 2 | 2450641 993 Tue Jun 10 18:32:01 1997 PDT | 199 | 20 | 2 | 2450611 994 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 995 Tue Feb 11 17:32:01 1997 PST | 199 | 20 | 2 | 2450492 996 Wed Feb 12 17:32:01 1997 PST | 199 | 20 | 2 | 2450493 997 Thu Feb 13 17:32:01 1997 PST | 199 | 20 | 2 | 2450494 998 Fri Feb 14 17:32:01 1997 PST | 199 | 20 | 2 | 2450495 999 Sat Feb 15 17:32:01 1997 PST | 199 | 20 | 2 | 2450496 1000 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 1001 Tue Feb 16 17:32:01 0097 PST BC | -10 | -1 | -1 | 1686043 1002 Sat Feb 16 17:32:01 0097 PST | 9 | 1 | 1 | 1756537 1003 Thu Feb 16 17:32:01 0597 PST | 59 | 6 | 1 | 1939158 1004 Tue Feb 16 17:32:01 1097 PST | 109 | 11 | 2 | 2121779 1005 Sat Feb 16 17:32:01 1697 PST | 169 | 17 | 2 | 2340925 1006 Thu Feb 16 17:32:01 1797 PST | 179 | 18 | 2 | 2377449 1007 Tue Feb 16 17:32:01 1897 PST | 189 | 19 | 2 | 2413973 1008 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 1009 Sat Feb 16 17:32:01 2097 PST | 209 | 21 | 3 | 2487022 1010 Wed Feb 28 17:32:01 1996 PST | 199 | 20 | 2 | 2450143 1011 Thu Feb 29 17:32:01 1996 PST | 199 | 20 | 2 | 2450144 1012 Fri Mar 01 17:32:01 1996 PST | 199 | 20 | 2 | 2450145 1013 Mon Dec 30 17:32:01 1996 PST | 199 | 20 | 2 | 2450449 1014 Tue Dec 31 17:32:01 1996 PST | 199 | 20 | 2 | 2450450 1015 Wed Jan 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450451 1016 Fri Feb 28 17:32:01 1997 PST | 199 | 20 | 2 | 2450509 1017 Sat Mar 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450510 1018 Tue Dec 30 17:32:01 1997 PST | 199 | 20 | 2 | 2450814 1019 Wed Dec 31 17:32:01 1997 PST | 199 | 20 | 2 | 2450815 1020 Fri Dec 31 17:32:01 1999 PST | 199 | 20 | 2 | 2451545 1021 Sat Jan 01 17:32:01 2000 PST | 200 | 20 | 2 | 2451546 1022 Sun Dec 31 17:32:01 2000 PST | 200 | 20 | 2 | 2451911 1023 Mon Jan 01 17:32:01 2001 PST | 200 | 21 | 3 | 2451912 1024(66 rows) 1025 1026SELECT d1 as timestamptz, 1027 date_part( 'timezone', d1) AS timezone, 1028 date_part( 'timezone_hour', d1) AS timezone_hour, 1029 date_part( 'timezone_minute', d1) AS timezone_minute 1030 FROM TIMESTAMPTZ_TBL; 1031 timestamptz | timezone | timezone_hour | timezone_minute 1032---------------------------------+----------+---------------+----------------- 1033 -infinity | | | 1034 infinity | | | 1035 Wed Dec 31 16:00:00 1969 PST | -28800 | -8 | 0 1036 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1037 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1038 Mon Feb 10 17:32:02 1997 PST | -28800 | -8 | 0 1039 Mon Feb 10 17:32:01.4 1997 PST | -28800 | -8 | 0 1040 Mon Feb 10 17:32:01.5 1997 PST | -28800 | -8 | 0 1041 Mon Feb 10 17:32:01.6 1997 PST | -28800 | -8 | 0 1042 Thu Jan 02 00:00:00 1997 PST | -28800 | -8 | 0 1043 Thu Jan 02 03:04:05 1997 PST | -28800 | -8 | 0 1044 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1045 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1046 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1047 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1048 Tue Jun 10 17:32:01 1997 PDT | -25200 | -7 | 0 1049 Sat Sep 22 18:19:20 2001 PDT | -25200 | -7 | 0 1050 Wed Mar 15 08:14:01 2000 PST | -28800 | -8 | 0 1051 Wed Mar 15 04:14:02 2000 PST | -28800 | -8 | 0 1052 Wed Mar 15 02:14:03 2000 PST | -28800 | -8 | 0 1053 Wed Mar 15 03:14:04 2000 PST | -28800 | -8 | 0 1054 Wed Mar 15 01:14:05 2000 PST | -28800 | -8 | 0 1055 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1056 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1057 Mon Feb 10 17:32:00 1997 PST | -28800 | -8 | 0 1058 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1059 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1060 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1061 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1062 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1063 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0 1064 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0 1065 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0 1066 Mon Feb 10 14:32:01 1997 PST | -28800 | -8 | 0 1067 Thu Jul 10 14:32:01 1997 PDT | -25200 | -7 | 0 1068 Tue Jun 10 18:32:01 1997 PDT | -25200 | -7 | 0 1069 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0 1070 Tue Feb 11 17:32:01 1997 PST | -28800 | -8 | 0 1071 Wed Feb 12 17:32:01 1997 PST | -28800 | -8 | 0 1072 Thu Feb 13 17:32:01 1997 PST | -28800 | -8 | 0 1073 Fri Feb 14 17:32:01 1997 PST | -28800 | -8 | 0 1074 Sat Feb 15 17:32:01 1997 PST | -28800 | -8 | 0 1075 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0 1076 Tue Feb 16 17:32:01 0097 PST BC | -28800 | -8 | 0 1077 Sat Feb 16 17:32:01 0097 PST | -28800 | -8 | 0 1078 Thu Feb 16 17:32:01 0597 PST | -28800 | -8 | 0 1079 Tue Feb 16 17:32:01 1097 PST | -28800 | -8 | 0 1080 Sat Feb 16 17:32:01 1697 PST | -28800 | -8 | 0 1081 Thu Feb 16 17:32:01 1797 PST | -28800 | -8 | 0 1082 Tue Feb 16 17:32:01 1897 PST | -28800 | -8 | 0 1083 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0 1084 Sat Feb 16 17:32:01 2097 PST | -28800 | -8 | 0 1085 Wed Feb 28 17:32:01 1996 PST | -28800 | -8 | 0 1086 Thu Feb 29 17:32:01 1996 PST | -28800 | -8 | 0 1087 Fri Mar 01 17:32:01 1996 PST | -28800 | -8 | 0 1088 Mon Dec 30 17:32:01 1996 PST | -28800 | -8 | 0 1089 Tue Dec 31 17:32:01 1996 PST | -28800 | -8 | 0 1090 Wed Jan 01 17:32:01 1997 PST | -28800 | -8 | 0 1091 Fri Feb 28 17:32:01 1997 PST | -28800 | -8 | 0 1092 Sat Mar 01 17:32:01 1997 PST | -28800 | -8 | 0 1093 Tue Dec 30 17:32:01 1997 PST | -28800 | -8 | 0 1094 Wed Dec 31 17:32:01 1997 PST | -28800 | -8 | 0 1095 Fri Dec 31 17:32:01 1999 PST | -28800 | -8 | 0 1096 Sat Jan 01 17:32:01 2000 PST | -28800 | -8 | 0 1097 Sun Dec 31 17:32:01 2000 PST | -28800 | -8 | 0 1098 Mon Jan 01 17:32:01 2001 PST | -28800 | -8 | 0 1099(66 rows) 1100 1101-- TO_CHAR() 1102SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') 1103 FROM TIMESTAMPTZ_TBL; 1104 to_char_1 | to_char 1105-----------+------------------------------------------------------------------------------------------ 1106 | 1107 | 1108 | WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec 1109 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1110 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1111 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1112 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1113 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1114 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1115 | THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan 1116 | THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan 1117 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1118 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1119 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1120 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1121 | TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun 1122 | SATURDAY Saturday saturday SAT Sat sat SEPTEMBER September september IX SEP Sep sep 1123 | WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar 1124 | WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar 1125 | WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar 1126 | WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar 1127 | WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar 1128 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1129 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1130 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1131 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1132 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1133 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1134 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1135 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1136 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1137 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1138 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1139 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1140 | THURSDAY Thursday thursday THU Thu thu JULY July july VII JUL Jul jul 1141 | TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun 1142 | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb 1143 | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb 1144 | WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb 1145 | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb 1146 | FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb 1147 | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb 1148 | SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb 1149 | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb 1150 | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb 1151 | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb 1152 | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb 1153 | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb 1154 | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb 1155 | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb 1156 | SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb 1157 | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb 1158 | WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb 1159 | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb 1160 | FRIDAY Friday friday FRI Fri fri MARCH March march III MAR Mar mar 1161 | MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec 1162 | TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec 1163 | WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan 1164 | FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb 1165 | SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar 1166 | TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec 1167 | WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec 1168 | FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec 1169 | SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan 1170 | SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec 1171 | MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan 1172(66 rows) 1173 1174SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM') 1175 FROM TIMESTAMPTZ_TBL; 1176 to_char_2 | to_char 1177-----------+-------------------------------------------------------------- 1178 | 1179 | 1180 | WEDNESDAY Wednesday wednesday DECEMBER December december XII 1181 | MONDAY Monday monday FEBRUARY February february II 1182 | MONDAY Monday monday FEBRUARY February february II 1183 | MONDAY Monday monday FEBRUARY February february II 1184 | MONDAY Monday monday FEBRUARY February february II 1185 | MONDAY Monday monday FEBRUARY February february II 1186 | MONDAY Monday monday FEBRUARY February february II 1187 | THURSDAY Thursday thursday JANUARY January january I 1188 | THURSDAY Thursday thursday JANUARY January january I 1189 | MONDAY Monday monday FEBRUARY February february II 1190 | MONDAY Monday monday FEBRUARY February february II 1191 | MONDAY Monday monday FEBRUARY February february II 1192 | MONDAY Monday monday FEBRUARY February february II 1193 | TUESDAY Tuesday tuesday JUNE June june VI 1194 | SATURDAY Saturday saturday SEPTEMBER September september IX 1195 | WEDNESDAY Wednesday wednesday MARCH March march III 1196 | WEDNESDAY Wednesday wednesday MARCH March march III 1197 | WEDNESDAY Wednesday wednesday MARCH March march III 1198 | WEDNESDAY Wednesday wednesday MARCH March march III 1199 | WEDNESDAY Wednesday wednesday MARCH March march III 1200 | MONDAY Monday monday FEBRUARY February february II 1201 | MONDAY Monday monday FEBRUARY February february II 1202 | MONDAY Monday monday FEBRUARY February february II 1203 | MONDAY Monday monday FEBRUARY February february II 1204 | MONDAY Monday monday FEBRUARY February february II 1205 | MONDAY Monday monday FEBRUARY February february II 1206 | MONDAY Monday monday FEBRUARY February february II 1207 | MONDAY Monday monday FEBRUARY February february II 1208 | MONDAY Monday monday FEBRUARY February february II 1209 | MONDAY Monday monday FEBRUARY February february II 1210 | MONDAY Monday monday FEBRUARY February february II 1211 | MONDAY Monday monday FEBRUARY February february II 1212 | THURSDAY Thursday thursday JULY July july VII 1213 | TUESDAY Tuesday tuesday JUNE June june VI 1214 | MONDAY Monday monday FEBRUARY February february II 1215 | TUESDAY Tuesday tuesday FEBRUARY February february II 1216 | WEDNESDAY Wednesday wednesday FEBRUARY February february II 1217 | THURSDAY Thursday thursday FEBRUARY February february II 1218 | FRIDAY Friday friday FEBRUARY February february II 1219 | SATURDAY Saturday saturday FEBRUARY February february II 1220 | SUNDAY Sunday sunday FEBRUARY February february II 1221 | TUESDAY Tuesday tuesday FEBRUARY February february II 1222 | SATURDAY Saturday saturday FEBRUARY February february II 1223 | THURSDAY Thursday thursday FEBRUARY February february II 1224 | TUESDAY Tuesday tuesday FEBRUARY February february II 1225 | SATURDAY Saturday saturday FEBRUARY February february II 1226 | THURSDAY Thursday thursday FEBRUARY February february II 1227 | TUESDAY Tuesday tuesday FEBRUARY February february II 1228 | SUNDAY Sunday sunday FEBRUARY February february II 1229 | SATURDAY Saturday saturday FEBRUARY February february II 1230 | WEDNESDAY Wednesday wednesday FEBRUARY February february II 1231 | THURSDAY Thursday thursday FEBRUARY February february II 1232 | FRIDAY Friday friday MARCH March march III 1233 | MONDAY Monday monday DECEMBER December december XII 1234 | TUESDAY Tuesday tuesday DECEMBER December december XII 1235 | WEDNESDAY Wednesday wednesday JANUARY January january I 1236 | FRIDAY Friday friday FEBRUARY February february II 1237 | SATURDAY Saturday saturday MARCH March march III 1238 | TUESDAY Tuesday tuesday DECEMBER December december XII 1239 | WEDNESDAY Wednesday wednesday DECEMBER December december XII 1240 | FRIDAY Friday friday DECEMBER December december XII 1241 | SATURDAY Saturday saturday JANUARY January january I 1242 | SUNDAY Sunday sunday DECEMBER December december XII 1243 | MONDAY Monday monday JANUARY January january I 1244(66 rows) 1245 1246SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J') 1247 FROM TIMESTAMPTZ_TBL; 1248 to_char_3 | to_char 1249-----------+-------------------------------------------------- 1250 | 1251 | 1252 | 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587 1253 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1254 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1255 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1256 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1257 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1258 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1259 | 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451 1260 | 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451 1261 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1262 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1263 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1264 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1265 | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610 1266 | 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175 1267 | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619 1268 | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619 1269 | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619 1270 | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619 1271 | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619 1272 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1273 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1274 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1275 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1276 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1277 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1278 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1279 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1280 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1281 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1282 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1283 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1284 | 1,997 1997 997 97 7 20 3 07 28 191 10 5 2450640 1285 | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610 1286 | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 1287 | 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491 1288 | 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492 1289 | 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493 1290 | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494 1291 | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495 1292 | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 1293 | 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042 1294 | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536 1295 | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157 1296 | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778 1297 | 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924 1298 | 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448 1299 | 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972 1300 | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 1301 | 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021 1302 | 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142 1303 | 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143 1304 | 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144 1305 | 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448 1306 | 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449 1307 | 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450 1308 | 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508 1309 | 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509 1310 | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813 1311 | 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814 1312 | 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544 1313 | 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545 1314 | 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910 1315 | 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911 1316(66 rows) 1317 1318SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ') 1319 FROM TIMESTAMPTZ_TBL; 1320 to_char_4 | to_char 1321-----------+------------------------------------------------- 1322 | 1323 | 1324 | 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587 1325 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1326 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1327 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1328 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1329 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1330 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1331 | 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451 1332 | 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451 1333 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1334 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1335 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1336 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1337 | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610 1338 | 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175 1339 | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619 1340 | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619 1341 | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619 1342 | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619 1343 | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619 1344 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1345 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1346 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1347 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1348 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1349 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1350 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1351 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1352 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1353 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1354 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1355 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1356 | 1,997 1997 997 97 7 20 3 7 28 191 10 5 2450640 1357 | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610 1358 | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 1359 | 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491 1360 | 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492 1361 | 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493 1362 | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 1363 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 1364 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 1365 | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042 1366 | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 1367 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 1368 | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778 1369 | 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924 1370 | 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448 1371 | 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972 1372 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 1373 | 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021 1374 | 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142 1375 | 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143 1376 | 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144 1377 | 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448 1378 | 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449 1379 | 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450 1380 | 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508 1381 | 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509 1382 | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813 1383 | 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814 1384 | 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544 1385 | 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545 1386 | 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910 1387 | 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911 1388(66 rows) 1389 1390SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') 1391 FROM TIMESTAMPTZ_TBL; 1392 to_char_5 | to_char 1393-----------+---------------------- 1394 | 1395 | 1396 | 04 04 16 00 00 57600 1397 | 05 05 17 32 01 63121 1398 | 05 05 17 32 01 63121 1399 | 05 05 17 32 02 63122 1400 | 05 05 17 32 01 63121 1401 | 05 05 17 32 01 63121 1402 | 05 05 17 32 01 63121 1403 | 12 12 00 00 00 0 1404 | 03 03 03 04 05 11045 1405 | 05 05 17 32 01 63121 1406 | 05 05 17 32 01 63121 1407 | 05 05 17 32 01 63121 1408 | 05 05 17 32 01 63121 1409 | 05 05 17 32 01 63121 1410 | 06 06 18 19 20 65960 1411 | 08 08 08 14 01 29641 1412 | 04 04 04 14 02 15242 1413 | 02 02 02 14 03 8043 1414 | 03 03 03 14 04 11644 1415 | 01 01 01 14 05 4445 1416 | 05 05 17 32 01 63121 1417 | 05 05 17 32 01 63121 1418 | 05 05 17 32 00 63120 1419 | 05 05 17 32 01 63121 1420 | 05 05 17 32 01 63121 1421 | 05 05 17 32 01 63121 1422 | 05 05 17 32 01 63121 1423 | 05 05 17 32 01 63121 1424 | 09 09 09 32 01 34321 1425 | 09 09 09 32 01 34321 1426 | 09 09 09 32 01 34321 1427 | 02 02 14 32 01 52321 1428 | 02 02 14 32 01 52321 1429 | 06 06 18 32 01 66721 1430 | 05 05 17 32 01 63121 1431 | 05 05 17 32 01 63121 1432 | 05 05 17 32 01 63121 1433 | 05 05 17 32 01 63121 1434 | 05 05 17 32 01 63121 1435 | 05 05 17 32 01 63121 1436 | 05 05 17 32 01 63121 1437 | 05 05 17 32 01 63121 1438 | 05 05 17 32 01 63121 1439 | 05 05 17 32 01 63121 1440 | 05 05 17 32 01 63121 1441 | 05 05 17 32 01 63121 1442 | 05 05 17 32 01 63121 1443 | 05 05 17 32 01 63121 1444 | 05 05 17 32 01 63121 1445 | 05 05 17 32 01 63121 1446 | 05 05 17 32 01 63121 1447 | 05 05 17 32 01 63121 1448 | 05 05 17 32 01 63121 1449 | 05 05 17 32 01 63121 1450 | 05 05 17 32 01 63121 1451 | 05 05 17 32 01 63121 1452 | 05 05 17 32 01 63121 1453 | 05 05 17 32 01 63121 1454 | 05 05 17 32 01 63121 1455 | 05 05 17 32 01 63121 1456 | 05 05 17 32 01 63121 1457 | 05 05 17 32 01 63121 1458 | 05 05 17 32 01 63121 1459 | 05 05 17 32 01 63121 1460(66 rows) 1461 1462SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""') 1463 FROM TIMESTAMPTZ_TBL; 1464 to_char_6 | to_char 1465-----------+------------------------------------------------- 1466 | 1467 | 1468 | HH:MI:SS is 04:00:00 "text between quote marks" 1469 | HH:MI:SS is 05:32:01 "text between quote marks" 1470 | HH:MI:SS is 05:32:01 "text between quote marks" 1471 | HH:MI:SS is 05:32:02 "text between quote marks" 1472 | HH:MI:SS is 05:32:01 "text between quote marks" 1473 | HH:MI:SS is 05:32:01 "text between quote marks" 1474 | HH:MI:SS is 05:32:01 "text between quote marks" 1475 | HH:MI:SS is 12:00:00 "text between quote marks" 1476 | HH:MI:SS is 03:04:05 "text between quote marks" 1477 | HH:MI:SS is 05:32:01 "text between quote marks" 1478 | HH:MI:SS is 05:32:01 "text between quote marks" 1479 | HH:MI:SS is 05:32:01 "text between quote marks" 1480 | HH:MI:SS is 05:32:01 "text between quote marks" 1481 | HH:MI:SS is 05:32:01 "text between quote marks" 1482 | HH:MI:SS is 06:19:20 "text between quote marks" 1483 | HH:MI:SS is 08:14:01 "text between quote marks" 1484 | HH:MI:SS is 04:14:02 "text between quote marks" 1485 | HH:MI:SS is 02:14:03 "text between quote marks" 1486 | HH:MI:SS is 03:14:04 "text between quote marks" 1487 | HH:MI:SS is 01:14:05 "text between quote marks" 1488 | HH:MI:SS is 05:32:01 "text between quote marks" 1489 | HH:MI:SS is 05:32:01 "text between quote marks" 1490 | HH:MI:SS is 05:32:00 "text between quote marks" 1491 | HH:MI:SS is 05:32:01 "text between quote marks" 1492 | HH:MI:SS is 05:32:01 "text between quote marks" 1493 | HH:MI:SS is 05:32:01 "text between quote marks" 1494 | HH:MI:SS is 05:32:01 "text between quote marks" 1495 | HH:MI:SS is 05:32:01 "text between quote marks" 1496 | HH:MI:SS is 09:32:01 "text between quote marks" 1497 | HH:MI:SS is 09:32:01 "text between quote marks" 1498 | HH:MI:SS is 09:32:01 "text between quote marks" 1499 | HH:MI:SS is 02:32:01 "text between quote marks" 1500 | HH:MI:SS is 02:32:01 "text between quote marks" 1501 | HH:MI:SS is 06:32:01 "text between quote marks" 1502 | HH:MI:SS is 05:32:01 "text between quote marks" 1503 | HH:MI:SS is 05:32:01 "text between quote marks" 1504 | HH:MI:SS is 05:32:01 "text between quote marks" 1505 | HH:MI:SS is 05:32:01 "text between quote marks" 1506 | HH:MI:SS is 05:32:01 "text between quote marks" 1507 | HH:MI:SS is 05:32:01 "text between quote marks" 1508 | HH:MI:SS is 05:32:01 "text between quote marks" 1509 | HH:MI:SS is 05:32:01 "text between quote marks" 1510 | HH:MI:SS is 05:32:01 "text between quote marks" 1511 | HH:MI:SS is 05:32:01 "text between quote marks" 1512 | HH:MI:SS is 05:32:01 "text between quote marks" 1513 | HH:MI:SS is 05:32:01 "text between quote marks" 1514 | HH:MI:SS is 05:32:01 "text between quote marks" 1515 | HH:MI:SS is 05:32:01 "text between quote marks" 1516 | HH:MI:SS is 05:32:01 "text between quote marks" 1517 | HH:MI:SS is 05:32:01 "text between quote marks" 1518 | HH:MI:SS is 05:32:01 "text between quote marks" 1519 | HH:MI:SS is 05:32:01 "text between quote marks" 1520 | HH:MI:SS is 05:32:01 "text between quote marks" 1521 | HH:MI:SS is 05:32:01 "text between quote marks" 1522 | HH:MI:SS is 05:32:01 "text between quote marks" 1523 | HH:MI:SS is 05:32:01 "text between quote marks" 1524 | HH:MI:SS is 05:32:01 "text between quote marks" 1525 | HH:MI:SS is 05:32:01 "text between quote marks" 1526 | HH:MI:SS is 05:32:01 "text between quote marks" 1527 | HH:MI:SS is 05:32:01 "text between quote marks" 1528 | HH:MI:SS is 05:32:01 "text between quote marks" 1529 | HH:MI:SS is 05:32:01 "text between quote marks" 1530 | HH:MI:SS is 05:32:01 "text between quote marks" 1531 | HH:MI:SS is 05:32:01 "text between quote marks" 1532(66 rows) 1533 1534SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS') 1535 FROM TIMESTAMPTZ_TBL; 1536 to_char_7 | to_char 1537-----------+------------------------ 1538 | 1539 | 1540 | 16--text--00--text--00 1541 | 17--text--32--text--01 1542 | 17--text--32--text--01 1543 | 17--text--32--text--02 1544 | 17--text--32--text--01 1545 | 17--text--32--text--01 1546 | 17--text--32--text--01 1547 | 00--text--00--text--00 1548 | 03--text--04--text--05 1549 | 17--text--32--text--01 1550 | 17--text--32--text--01 1551 | 17--text--32--text--01 1552 | 17--text--32--text--01 1553 | 17--text--32--text--01 1554 | 18--text--19--text--20 1555 | 08--text--14--text--01 1556 | 04--text--14--text--02 1557 | 02--text--14--text--03 1558 | 03--text--14--text--04 1559 | 01--text--14--text--05 1560 | 17--text--32--text--01 1561 | 17--text--32--text--01 1562 | 17--text--32--text--00 1563 | 17--text--32--text--01 1564 | 17--text--32--text--01 1565 | 17--text--32--text--01 1566 | 17--text--32--text--01 1567 | 17--text--32--text--01 1568 | 09--text--32--text--01 1569 | 09--text--32--text--01 1570 | 09--text--32--text--01 1571 | 14--text--32--text--01 1572 | 14--text--32--text--01 1573 | 18--text--32--text--01 1574 | 17--text--32--text--01 1575 | 17--text--32--text--01 1576 | 17--text--32--text--01 1577 | 17--text--32--text--01 1578 | 17--text--32--text--01 1579 | 17--text--32--text--01 1580 | 17--text--32--text--01 1581 | 17--text--32--text--01 1582 | 17--text--32--text--01 1583 | 17--text--32--text--01 1584 | 17--text--32--text--01 1585 | 17--text--32--text--01 1586 | 17--text--32--text--01 1587 | 17--text--32--text--01 1588 | 17--text--32--text--01 1589 | 17--text--32--text--01 1590 | 17--text--32--text--01 1591 | 17--text--32--text--01 1592 | 17--text--32--text--01 1593 | 17--text--32--text--01 1594 | 17--text--32--text--01 1595 | 17--text--32--text--01 1596 | 17--text--32--text--01 1597 | 17--text--32--text--01 1598 | 17--text--32--text--01 1599 | 17--text--32--text--01 1600 | 17--text--32--text--01 1601 | 17--text--32--text--01 1602 | 17--text--32--text--01 1603 | 17--text--32--text--01 1604(66 rows) 1605 1606SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth') 1607 FROM TIMESTAMPTZ_TBL; 1608 to_char_8 | to_char 1609-----------+------------------------- 1610 | 1611 | 1612 | 1969TH 1969th 2440587th 1613 | 1997TH 1997th 2450490th 1614 | 1997TH 1997th 2450490th 1615 | 1997TH 1997th 2450490th 1616 | 1997TH 1997th 2450490th 1617 | 1997TH 1997th 2450490th 1618 | 1997TH 1997th 2450490th 1619 | 1997TH 1997th 2450451st 1620 | 1997TH 1997th 2450451st 1621 | 1997TH 1997th 2450490th 1622 | 1997TH 1997th 2450490th 1623 | 1997TH 1997th 2450490th 1624 | 1997TH 1997th 2450490th 1625 | 1997TH 1997th 2450610th 1626 | 2001ST 2001st 2452175th 1627 | 2000TH 2000th 2451619th 1628 | 2000TH 2000th 2451619th 1629 | 2000TH 2000th 2451619th 1630 | 2000TH 2000th 2451619th 1631 | 2000TH 2000th 2451619th 1632 | 1997TH 1997th 2450490th 1633 | 1997TH 1997th 2450490th 1634 | 1997TH 1997th 2450490th 1635 | 1997TH 1997th 2450490th 1636 | 1997TH 1997th 2450490th 1637 | 1997TH 1997th 2450490th 1638 | 1997TH 1997th 2450490th 1639 | 1997TH 1997th 2450490th 1640 | 1997TH 1997th 2450490th 1641 | 1997TH 1997th 2450490th 1642 | 1997TH 1997th 2450490th 1643 | 1997TH 1997th 2450490th 1644 | 1997TH 1997th 2450640th 1645 | 1997TH 1997th 2450610th 1646 | 1997TH 1997th 2450490th 1647 | 1997TH 1997th 2450491st 1648 | 1997TH 1997th 2450492nd 1649 | 1997TH 1997th 2450493rd 1650 | 1997TH 1997th 2450494th 1651 | 1997TH 1997th 2450495th 1652 | 1997TH 1997th 2450496th 1653 | 0097TH 0097th 1686042nd 1654 | 0097TH 0097th 1756536th 1655 | 0597TH 0597th 1939157th 1656 | 1097TH 1097th 2121778th 1657 | 1697TH 1697th 2340924th 1658 | 1797TH 1797th 2377448th 1659 | 1897TH 1897th 2413972nd 1660 | 1997TH 1997th 2450496th 1661 | 2097TH 2097th 2487021st 1662 | 1996TH 1996th 2450142nd 1663 | 1996TH 1996th 2450143rd 1664 | 1996TH 1996th 2450144th 1665 | 1996TH 1996th 2450448th 1666 | 1996TH 1996th 2450449th 1667 | 1997TH 1997th 2450450th 1668 | 1997TH 1997th 2450508th 1669 | 1997TH 1997th 2450509th 1670 | 1997TH 1997th 2450813th 1671 | 1997TH 1997th 2450814th 1672 | 1999TH 1999th 2451544th 1673 | 2000TH 2000th 2451545th 1674 | 2000TH 2000th 2451910th 1675 | 2001ST 2001st 2451911th 1676(66 rows) 1677 1678SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm') 1679 FROM TIMESTAMPTZ_TBL; 1680 to_char_9 | to_char 1681-----------+--------------------------------------------------------------------- 1682 | 1683 | 1684 | 1969 A.D. 1969 a.d. 1969 ad 04:00:00 P.M. 04:00:00 p.m. 04:00:00 pm 1685 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1686 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1687 | 1997 A.D. 1997 a.d. 1997 ad 05:32:02 P.M. 05:32:02 p.m. 05:32:02 pm 1688 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1689 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1690 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1691 | 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am 1692 | 1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am 1693 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1694 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1695 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1696 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1697 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1698 | 2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm 1699 | 2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am 1700 | 2000 A.D. 2000 a.d. 2000 ad 04:14:02 A.M. 04:14:02 a.m. 04:14:02 am 1701 | 2000 A.D. 2000 a.d. 2000 ad 02:14:03 A.M. 02:14:03 a.m. 02:14:03 am 1702 | 2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am 1703 | 2000 A.D. 2000 a.d. 2000 ad 01:14:05 A.M. 01:14:05 a.m. 01:14:05 am 1704 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1705 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1706 | 1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm 1707 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1708 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1709 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1710 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1711 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1712 | 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am 1713 | 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am 1714 | 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am 1715 | 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm 1716 | 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm 1717 | 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm 1718 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1719 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1720 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1721 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1722 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1723 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1724 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1725 | 0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1726 | 0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1727 | 0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1728 | 1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1729 | 1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1730 | 1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1731 | 1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1732 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1733 | 2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1734 | 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1735 | 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1736 | 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1737 | 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1738 | 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1739 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1740 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1741 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1742 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1743 | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1744 | 1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1745 | 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1746 | 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1747 | 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm 1748(66 rows) 1749 1750SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID') 1751 FROM TIMESTAMPTZ_TBL; 1752 to_char_10 | to_char 1753------------+------------------------ 1754 | 1755 | 1756 | 1970 970 70 0 01 003 3 1757 | 1997 997 97 7 07 043 1 1758 | 1997 997 97 7 07 043 1 1759 | 1997 997 97 7 07 043 1 1760 | 1997 997 97 7 07 043 1 1761 | 1997 997 97 7 07 043 1 1762 | 1997 997 97 7 07 043 1 1763 | 1997 997 97 7 01 004 4 1764 | 1997 997 97 7 01 004 4 1765 | 1997 997 97 7 07 043 1 1766 | 1997 997 97 7 07 043 1 1767 | 1997 997 97 7 07 043 1 1768 | 1997 997 97 7 07 043 1 1769 | 1997 997 97 7 24 163 2 1770 | 2001 001 01 1 38 265 6 1771 | 2000 000 00 0 11 073 3 1772 | 2000 000 00 0 11 073 3 1773 | 2000 000 00 0 11 073 3 1774 | 2000 000 00 0 11 073 3 1775 | 2000 000 00 0 11 073 3 1776 | 1997 997 97 7 07 043 1 1777 | 1997 997 97 7 07 043 1 1778 | 1997 997 97 7 07 043 1 1779 | 1997 997 97 7 07 043 1 1780 | 1997 997 97 7 07 043 1 1781 | 1997 997 97 7 07 043 1 1782 | 1997 997 97 7 07 043 1 1783 | 1997 997 97 7 07 043 1 1784 | 1997 997 97 7 07 043 1 1785 | 1997 997 97 7 07 043 1 1786 | 1997 997 97 7 07 043 1 1787 | 1997 997 97 7 07 043 1 1788 | 1997 997 97 7 28 193 4 1789 | 1997 997 97 7 24 163 2 1790 | 1997 997 97 7 07 043 1 1791 | 1997 997 97 7 07 044 2 1792 | 1997 997 97 7 07 045 3 1793 | 1997 997 97 7 07 046 4 1794 | 1997 997 97 7 07 047 5 1795 | 1997 997 97 7 07 048 6 1796 | 1997 997 97 7 07 049 7 1797 | 0097 097 97 7 07 044 2 1798 | 0097 097 97 7 07 048 6 1799 | 0597 597 97 7 07 046 4 1800 | 1097 097 97 7 07 044 2 1801 | 1697 697 97 7 07 048 6 1802 | 1797 797 97 7 07 046 4 1803 | 1897 897 97 7 07 044 2 1804 | 1997 997 97 7 07 049 7 1805 | 2097 097 97 7 07 048 6 1806 | 1996 996 96 6 09 059 3 1807 | 1996 996 96 6 09 060 4 1808 | 1996 996 96 6 09 061 5 1809 | 1997 997 97 7 01 001 1 1810 | 1997 997 97 7 01 002 2 1811 | 1997 997 97 7 01 003 3 1812 | 1997 997 97 7 09 061 5 1813 | 1997 997 97 7 09 062 6 1814 | 1998 998 98 8 01 002 2 1815 | 1998 998 98 8 01 003 3 1816 | 1999 999 99 9 52 362 5 1817 | 1999 999 99 9 52 363 6 1818 | 2000 000 00 0 52 364 7 1819 | 2001 001 01 1 01 001 1 1820(66 rows) 1821 1822SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') 1823 FROM TIMESTAMPTZ_TBL; 1824 to_char_11 | to_char 1825------------+------------------------ 1826 | 1827 | 1828 | 1970 970 70 0 1 3 3 1829 | 1997 997 97 7 7 43 1 1830 | 1997 997 97 7 7 43 1 1831 | 1997 997 97 7 7 43 1 1832 | 1997 997 97 7 7 43 1 1833 | 1997 997 97 7 7 43 1 1834 | 1997 997 97 7 7 43 1 1835 | 1997 997 97 7 1 4 4 1836 | 1997 997 97 7 1 4 4 1837 | 1997 997 97 7 7 43 1 1838 | 1997 997 97 7 7 43 1 1839 | 1997 997 97 7 7 43 1 1840 | 1997 997 97 7 7 43 1 1841 | 1997 997 97 7 24 163 2 1842 | 2001 1 1 1 38 265 6 1843 | 2000 0 0 0 11 73 3 1844 | 2000 0 0 0 11 73 3 1845 | 2000 0 0 0 11 73 3 1846 | 2000 0 0 0 11 73 3 1847 | 2000 0 0 0 11 73 3 1848 | 1997 997 97 7 7 43 1 1849 | 1997 997 97 7 7 43 1 1850 | 1997 997 97 7 7 43 1 1851 | 1997 997 97 7 7 43 1 1852 | 1997 997 97 7 7 43 1 1853 | 1997 997 97 7 7 43 1 1854 | 1997 997 97 7 7 43 1 1855 | 1997 997 97 7 7 43 1 1856 | 1997 997 97 7 7 43 1 1857 | 1997 997 97 7 7 43 1 1858 | 1997 997 97 7 7 43 1 1859 | 1997 997 97 7 7 43 1 1860 | 1997 997 97 7 28 193 4 1861 | 1997 997 97 7 24 163 2 1862 | 1997 997 97 7 7 43 1 1863 | 1997 997 97 7 7 44 2 1864 | 1997 997 97 7 7 45 3 1865 | 1997 997 97 7 7 46 4 1866 | 1997 997 97 7 7 47 5 1867 | 1997 997 97 7 7 48 6 1868 | 1997 997 97 7 7 49 7 1869 | 97 97 97 7 7 44 2 1870 | 97 97 97 7 7 48 6 1871 | 597 597 97 7 7 46 4 1872 | 1097 97 97 7 7 44 2 1873 | 1697 697 97 7 7 48 6 1874 | 1797 797 97 7 7 46 4 1875 | 1897 897 97 7 7 44 2 1876 | 1997 997 97 7 7 49 7 1877 | 2097 97 97 7 7 48 6 1878 | 1996 996 96 6 9 59 3 1879 | 1996 996 96 6 9 60 4 1880 | 1996 996 96 6 9 61 5 1881 | 1997 997 97 7 1 1 1 1882 | 1997 997 97 7 1 2 2 1883 | 1997 997 97 7 1 3 3 1884 | 1997 997 97 7 9 61 5 1885 | 1997 997 97 7 9 62 6 1886 | 1998 998 98 8 1 2 2 1887 | 1998 998 98 8 1 3 3 1888 | 1999 999 99 9 52 362 5 1889 | 1999 999 99 9 52 363 6 1890 | 2000 0 0 0 52 364 7 1891 | 2001 1 1 1 1 1 1 1892(66 rows) 1893 1894-- Check OF with various zone offsets, particularly fractional hours 1895SET timezone = '00:00'; 1896SELECT to_char(now(), 'OF'); 1897 to_char 1898--------- 1899 +00 1900(1 row) 1901 1902SET timezone = '+02:00'; 1903SELECT to_char(now(), 'OF'); 1904 to_char 1905--------- 1906 -02 1907(1 row) 1908 1909SET timezone = '-13:00'; 1910SELECT to_char(now(), 'OF'); 1911 to_char 1912--------- 1913 +13 1914(1 row) 1915 1916SET timezone = '-00:30'; 1917SELECT to_char(now(), 'OF'); 1918 to_char 1919--------- 1920 +00:30 1921(1 row) 1922 1923SET timezone = '00:30'; 1924SELECT to_char(now(), 'OF'); 1925 to_char 1926--------- 1927 -00:30 1928(1 row) 1929 1930SET timezone = '-04:30'; 1931SELECT to_char(now(), 'OF'); 1932 to_char 1933--------- 1934 +04:30 1935(1 row) 1936 1937SET timezone = '04:30'; 1938SELECT to_char(now(), 'OF'); 1939 to_char 1940--------- 1941 -04:30 1942(1 row) 1943 1944RESET timezone; 1945CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); 1946-- Test year field value with len > 4 1947INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST'); 1948INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST'); 1949INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST'); 1950INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST'); 1951INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST'); 1952INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST'); 1953--Verify data 1954SELECT * FROM TIMESTAMPTZ_TST ORDER BY a; 1955 a | b 1956---+-------------------------------- 1957 1 | Wed Mar 12 13:58:48 1000 PST 1958 2 | Sun Mar 12 14:58:48 10000 PDT 1959 3 | Sun Mar 12 14:58:48 100000 PDT 1960 3 | Sun Mar 12 14:58:48 10000 PDT 1961 4 | Sun Mar 12 14:58:48 10000 PDT 1962 4 | Sun Mar 12 14:58:48 100000 PDT 1963(6 rows) 1964 1965--Cleanup 1966DROP TABLE TIMESTAMPTZ_TST; 1967-- test timestamptz constructors 1968set TimeZone to 'America/New_York'; 1969-- numeric timezone 1970SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33); 1971 make_timestamptz 1972--------------------------------- 1973 Sun Jul 15 08:15:55.33 1973 EDT 1974(1 row) 1975 1976SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2'); 1977 make_timestamptz 1978--------------------------------- 1979 Sun Jul 15 02:15:55.33 1973 EDT 1980(1 row) 1981 1982SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '-2'); 1983 make_timestamptz 1984--------------------------------- 1985 Sun Jul 15 06:15:55.33 1973 EDT 1986(1 row) 1987 1988WITH tzs (tz) AS (VALUES 1989 ('+1'), ('+1:'), ('+1:0'), ('+100'), ('+1:00'), ('+01:00'), 1990 ('+10'), ('+1000'), ('+10:'), ('+10:0'), ('+10:00'), ('+10:00:'), 1991 ('+10:00:1'), ('+10:00:01'), 1992 ('+10:00:10')) 1993 SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs; 1994 make_timestamptz | tz 1995------------------------------+----------- 1996 Fri Feb 26 21:45:00 2010 EST | +1 1997 Fri Feb 26 21:45:00 2010 EST | +1: 1998 Fri Feb 26 21:45:00 2010 EST | +1:0 1999 Fri Feb 26 21:45:00 2010 EST | +100 2000 Fri Feb 26 21:45:00 2010 EST | +1:00 2001 Fri Feb 26 21:45:00 2010 EST | +01:00 2002 Fri Feb 26 12:45:00 2010 EST | +10 2003 Fri Feb 26 12:45:00 2010 EST | +1000 2004 Fri Feb 26 12:45:00 2010 EST | +10: 2005 Fri Feb 26 12:45:00 2010 EST | +10:0 2006 Fri Feb 26 12:45:00 2010 EST | +10:00 2007 Fri Feb 26 12:45:00 2010 EST | +10:00: 2008 Fri Feb 26 12:44:59 2010 EST | +10:00:1 2009 Fri Feb 26 12:44:59 2010 EST | +10:00:01 2010 Fri Feb 26 12:44:50 2010 EST | +10:00:10 2011(15 rows) 2012 2013-- these should fail 2014SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2'); 2015ERROR: invalid input syntax for numeric time zone: "2" 2016HINT: Numeric time zones must have "-" or "+" as first character. 2017SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '+16'); 2018ERROR: numeric time zone "+16" out of range 2019SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '-16'); 2020ERROR: numeric time zone "-16" out of range 2021-- should be true 2022SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz; 2023 ?column? 2024---------- 2025 t 2026(1 row) 2027 2028-- full timezone names 2029SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') = timestamptz '2014-12-10 00:00:00 Europe/Prague'; 2030 ?column? 2031---------- 2032 t 2033(1 row) 2034 2035SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') AT TIME ZONE 'UTC'; 2036 timezone 2037-------------------------- 2038 Tue Dec 09 23:00:00 2014 2039(1 row) 2040 2041SELECT make_timestamptz(1846, 12, 10, 0, 0, 0, 'Asia/Manila') AT TIME ZONE 'UTC'; 2042 timezone 2043-------------------------- 2044 Wed Dec 09 15:56:00 1846 2045(1 row) 2046 2047SELECT make_timestamptz(1881, 12, 10, 0, 0, 0, 'Europe/Paris') AT TIME ZONE 'UTC'; 2048 timezone 2049-------------------------- 2050 Fri Dec 09 23:50:39 1881 2051(1 row) 2052 2053SELECT make_timestamptz(1910, 12, 24, 0, 0, 0, 'Nehwon/Lankhmar'); 2054ERROR: time zone "Nehwon/Lankhmar" not recognized 2055-- abbreviations 2056SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EST'); 2057 make_timestamptz 2058------------------------------ 2059 Wed Dec 10 10:10:10 2008 EST 2060(1 row) 2061 2062SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EDT'); 2063 make_timestamptz 2064------------------------------ 2065 Wed Dec 10 09:10:10 2008 EST 2066(1 row) 2067 2068SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT'); 2069 make_timestamptz 2070------------------------------ 2071 Wed Dec 10 13:10:10 2014 EST 2072(1 row) 2073 2074RESET TimeZone; 2075-- 2076-- Test behavior with a dynamic (time-varying) timezone abbreviation. 2077-- These tests rely on the knowledge that MSK (Europe/Moscow standard time) 2078-- moved forwards in Mar 2011 and backwards again in Oct 2014. 2079-- 2080SET TimeZone to 'UTC'; 2081SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz; 2082 timestamptz 2083------------------------------ 2084 Sat Mar 26 21:00:00 2011 UTC 2085(1 row) 2086 2087SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz; 2088 timestamptz 2089------------------------------ 2090 Sat Mar 26 22:00:00 2011 UTC 2091(1 row) 2092 2093SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz; 2094 timestamptz 2095------------------------------ 2096 Sat Mar 26 22:59:59 2011 UTC 2097(1 row) 2098 2099SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz; 2100 timestamptz 2101------------------------------ 2102 Sat Mar 26 23:00:00 2011 UTC 2103(1 row) 2104 2105SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz; 2106 timestamptz 2107------------------------------ 2108 Sat Mar 26 23:00:01 2011 UTC 2109(1 row) 2110 2111SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz; 2112 timestamptz 2113------------------------------ 2114 Sat Mar 26 23:59:59 2011 UTC 2115(1 row) 2116 2117SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz; 2118 timestamptz 2119------------------------------ 2120 Sat Mar 26 23:00:00 2011 UTC 2121(1 row) 2122 2123SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz; 2124 timestamptz 2125------------------------------ 2126 Sat Mar 26 23:00:01 2011 UTC 2127(1 row) 2128 2129SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz; 2130 timestamptz 2131------------------------------ 2132 Sun Mar 27 00:00:00 2011 UTC 2133(1 row) 2134 2135SELECT '2011-03-27 00:00:00 MSK'::timestamptz; 2136 timestamptz 2137------------------------------ 2138 Sat Mar 26 21:00:00 2011 UTC 2139(1 row) 2140 2141SELECT '2011-03-27 01:00:00 MSK'::timestamptz; 2142 timestamptz 2143------------------------------ 2144 Sat Mar 26 22:00:00 2011 UTC 2145(1 row) 2146 2147SELECT '2011-03-27 01:59:59 MSK'::timestamptz; 2148 timestamptz 2149------------------------------ 2150 Sat Mar 26 22:59:59 2011 UTC 2151(1 row) 2152 2153SELECT '2011-03-27 02:00:00 MSK'::timestamptz; 2154 timestamptz 2155------------------------------ 2156 Sat Mar 26 22:00:00 2011 UTC 2157(1 row) 2158 2159SELECT '2011-03-27 02:00:01 MSK'::timestamptz; 2160 timestamptz 2161------------------------------ 2162 Sat Mar 26 22:00:01 2011 UTC 2163(1 row) 2164 2165SELECT '2011-03-27 02:59:59 MSK'::timestamptz; 2166 timestamptz 2167------------------------------ 2168 Sat Mar 26 22:59:59 2011 UTC 2169(1 row) 2170 2171SELECT '2011-03-27 03:00:00 MSK'::timestamptz; 2172 timestamptz 2173------------------------------ 2174 Sat Mar 26 23:00:00 2011 UTC 2175(1 row) 2176 2177SELECT '2011-03-27 03:00:01 MSK'::timestamptz; 2178 timestamptz 2179------------------------------ 2180 Sat Mar 26 23:00:01 2011 UTC 2181(1 row) 2182 2183SELECT '2011-03-27 04:00:00 MSK'::timestamptz; 2184 timestamptz 2185------------------------------ 2186 Sun Mar 27 00:00:00 2011 UTC 2187(1 row) 2188 2189SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz; 2190 timestamptz 2191------------------------------ 2192 Sat Oct 25 20:00:00 2014 UTC 2193(1 row) 2194 2195SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz; 2196 timestamptz 2197------------------------------ 2198 Sat Oct 25 20:59:59 2014 UTC 2199(1 row) 2200 2201SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz; 2202 timestamptz 2203------------------------------ 2204 Sat Oct 25 22:00:00 2014 UTC 2205(1 row) 2206 2207SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz; 2208 timestamptz 2209------------------------------ 2210 Sat Oct 25 22:00:01 2014 UTC 2211(1 row) 2212 2213SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz; 2214 timestamptz 2215------------------------------ 2216 Sat Oct 25 23:00:00 2014 UTC 2217(1 row) 2218 2219SELECT '2014-10-26 00:00:00 MSK'::timestamptz; 2220 timestamptz 2221------------------------------ 2222 Sat Oct 25 20:00:00 2014 UTC 2223(1 row) 2224 2225SELECT '2014-10-26 00:59:59 MSK'::timestamptz; 2226 timestamptz 2227------------------------------ 2228 Sat Oct 25 20:59:59 2014 UTC 2229(1 row) 2230 2231SELECT '2014-10-26 01:00:00 MSK'::timestamptz; 2232 timestamptz 2233------------------------------ 2234 Sat Oct 25 22:00:00 2014 UTC 2235(1 row) 2236 2237SELECT '2014-10-26 01:00:01 MSK'::timestamptz; 2238 timestamptz 2239------------------------------ 2240 Sat Oct 25 22:00:01 2014 UTC 2241(1 row) 2242 2243SELECT '2014-10-26 02:00:00 MSK'::timestamptz; 2244 timestamptz 2245------------------------------ 2246 Sat Oct 25 23:00:00 2014 UTC 2247(1 row) 2248 2249SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2250 timezone 2251------------------------------ 2252 Sat Mar 26 21:00:00 2011 UTC 2253(1 row) 2254 2255SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2256 timezone 2257------------------------------ 2258 Sat Mar 26 22:00:00 2011 UTC 2259(1 row) 2260 2261SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; 2262 timezone 2263------------------------------ 2264 Sat Mar 26 22:59:59 2011 UTC 2265(1 row) 2266 2267SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2268 timezone 2269------------------------------ 2270 Sat Mar 26 23:00:00 2011 UTC 2271(1 row) 2272 2273SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; 2274 timezone 2275------------------------------ 2276 Sat Mar 26 23:00:01 2011 UTC 2277(1 row) 2278 2279SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; 2280 timezone 2281------------------------------ 2282 Sat Mar 26 23:59:59 2011 UTC 2283(1 row) 2284 2285SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2286 timezone 2287------------------------------ 2288 Sat Mar 26 23:00:00 2011 UTC 2289(1 row) 2290 2291SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; 2292 timezone 2293------------------------------ 2294 Sat Mar 26 23:00:01 2011 UTC 2295(1 row) 2296 2297SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2298 timezone 2299------------------------------ 2300 Sun Mar 27 00:00:00 2011 UTC 2301(1 row) 2302 2303SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK'; 2304 timezone 2305------------------------------ 2306 Sat Mar 26 21:00:00 2011 UTC 2307(1 row) 2308 2309SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK'; 2310 timezone 2311------------------------------ 2312 Sat Mar 26 22:00:00 2011 UTC 2313(1 row) 2314 2315SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK'; 2316 timezone 2317------------------------------ 2318 Sat Mar 26 22:59:59 2011 UTC 2319(1 row) 2320 2321SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK'; 2322 timezone 2323------------------------------ 2324 Sat Mar 26 22:00:00 2011 UTC 2325(1 row) 2326 2327SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK'; 2328 timezone 2329------------------------------ 2330 Sat Mar 26 22:00:01 2011 UTC 2331(1 row) 2332 2333SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK'; 2334 timezone 2335------------------------------ 2336 Sat Mar 26 22:59:59 2011 UTC 2337(1 row) 2338 2339SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK'; 2340 timezone 2341------------------------------ 2342 Sat Mar 26 23:00:00 2011 UTC 2343(1 row) 2344 2345SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK'; 2346 timezone 2347------------------------------ 2348 Sat Mar 26 23:00:01 2011 UTC 2349(1 row) 2350 2351SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK'; 2352 timezone 2353------------------------------ 2354 Sun Mar 27 00:00:00 2011 UTC 2355(1 row) 2356 2357SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2358 timezone 2359------------------------------ 2360 Sat Oct 25 20:00:00 2014 UTC 2361(1 row) 2362 2363SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; 2364 timezone 2365------------------------------ 2366 Sat Oct 25 20:59:59 2014 UTC 2367(1 row) 2368 2369SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2370 timezone 2371------------------------------ 2372 Sat Oct 25 22:00:00 2014 UTC 2373(1 row) 2374 2375SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; 2376 timezone 2377------------------------------ 2378 Sat Oct 25 22:00:01 2014 UTC 2379(1 row) 2380 2381SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; 2382 timezone 2383------------------------------ 2384 Sat Oct 25 23:00:00 2014 UTC 2385(1 row) 2386 2387SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK'; 2388 timezone 2389------------------------------ 2390 Sat Oct 25 20:00:00 2014 UTC 2391(1 row) 2392 2393SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK'; 2394 timezone 2395------------------------------ 2396 Sat Oct 25 20:59:59 2014 UTC 2397(1 row) 2398 2399SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK'; 2400 timezone 2401------------------------------ 2402 Sat Oct 25 22:00:00 2014 UTC 2403(1 row) 2404 2405SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK'; 2406 timezone 2407------------------------------ 2408 Sat Oct 25 22:00:01 2014 UTC 2409(1 row) 2410 2411SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK'; 2412 timezone 2413------------------------------ 2414 Sat Oct 25 23:00:00 2014 UTC 2415(1 row) 2416 2417SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK'); 2418 make_timestamptz 2419------------------------------ 2420 Sat Oct 25 20:00:00 2014 UTC 2421(1 row) 2422 2423SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK'); 2424 make_timestamptz 2425------------------------------ 2426 Sat Oct 25 22:00:00 2014 UTC 2427(1 row) 2428 2429SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00 2430 to_timestamp 2431------------------------------ 2432 Thu Jan 01 00:00:00 1970 UTC 2433(1 row) 2434 2435SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00 2436 to_timestamp 2437------------------------------ 2438 Sat Jan 01 00:00:00 2000 UTC 2439(1 row) 2440 2441SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00 2442 to_timestamp 2443------------------------------------- 2444 Fri Jan 01 12:34:56.789012 2010 UTC 2445(1 row) 2446 2447-- edge cases 2448SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC 2449 to_timestamp 2450--------------------------------- 2451 Mon Nov 24 00:00:00 4714 UTC BC 2452(1 row) 2453 2454-- upper limit varies between integer and float timestamps, so hard to test 2455-- nonfinite values 2456SELECT to_timestamp(' Infinity'::float); 2457 to_timestamp 2458-------------- 2459 infinity 2460(1 row) 2461 2462SELECT to_timestamp('-Infinity'::float); 2463 to_timestamp 2464-------------- 2465 -infinity 2466(1 row) 2467 2468SELECT to_timestamp('NaN'::float); 2469ERROR: timestamp cannot be NaN 2470SET TimeZone to 'Europe/Moscow'; 2471SELECT '2011-03-26 21:00:00 UTC'::timestamptz; 2472 timestamptz 2473------------------------------ 2474 Sun Mar 27 00:00:00 2011 MSK 2475(1 row) 2476 2477SELECT '2011-03-26 22:00:00 UTC'::timestamptz; 2478 timestamptz 2479------------------------------ 2480 Sun Mar 27 01:00:00 2011 MSK 2481(1 row) 2482 2483SELECT '2011-03-26 22:59:59 UTC'::timestamptz; 2484 timestamptz 2485------------------------------ 2486 Sun Mar 27 01:59:59 2011 MSK 2487(1 row) 2488 2489SELECT '2011-03-26 23:00:00 UTC'::timestamptz; 2490 timestamptz 2491------------------------------ 2492 Sun Mar 27 03:00:00 2011 MSK 2493(1 row) 2494 2495SELECT '2011-03-26 23:00:01 UTC'::timestamptz; 2496 timestamptz 2497------------------------------ 2498 Sun Mar 27 03:00:01 2011 MSK 2499(1 row) 2500 2501SELECT '2011-03-26 23:59:59 UTC'::timestamptz; 2502 timestamptz 2503------------------------------ 2504 Sun Mar 27 03:59:59 2011 MSK 2505(1 row) 2506 2507SELECT '2011-03-27 00:00:00 UTC'::timestamptz; 2508 timestamptz 2509------------------------------ 2510 Sun Mar 27 04:00:00 2011 MSK 2511(1 row) 2512 2513SELECT '2014-10-25 21:00:00 UTC'::timestamptz; 2514 timestamptz 2515------------------------------ 2516 Sun Oct 26 01:00:00 2014 MSK 2517(1 row) 2518 2519SELECT '2014-10-25 21:59:59 UTC'::timestamptz; 2520 timestamptz 2521------------------------------ 2522 Sun Oct 26 01:59:59 2014 MSK 2523(1 row) 2524 2525SELECT '2014-10-25 22:00:00 UTC'::timestamptz; 2526 timestamptz 2527------------------------------ 2528 Sun Oct 26 01:00:00 2014 MSK 2529(1 row) 2530 2531SELECT '2014-10-25 22:00:01 UTC'::timestamptz; 2532 timestamptz 2533------------------------------ 2534 Sun Oct 26 01:00:01 2014 MSK 2535(1 row) 2536 2537SELECT '2014-10-25 23:00:00 UTC'::timestamptz; 2538 timestamptz 2539------------------------------ 2540 Sun Oct 26 02:00:00 2014 MSK 2541(1 row) 2542 2543RESET TimeZone; 2544SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2545 timezone 2546-------------------------- 2547 Sun Mar 27 00:00:00 2011 2548(1 row) 2549 2550SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2551 timezone 2552-------------------------- 2553 Sun Mar 27 01:00:00 2011 2554(1 row) 2555 2556SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2557 timezone 2558-------------------------- 2559 Sun Mar 27 01:59:59 2011 2560(1 row) 2561 2562SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2563 timezone 2564-------------------------- 2565 Sun Mar 27 03:00:00 2011 2566(1 row) 2567 2568SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2569 timezone 2570-------------------------- 2571 Sun Mar 27 03:00:01 2011 2572(1 row) 2573 2574SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2575 timezone 2576-------------------------- 2577 Sun Mar 27 03:59:59 2011 2578(1 row) 2579 2580SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2581 timezone 2582-------------------------- 2583 Sun Mar 27 04:00:00 2011 2584(1 row) 2585 2586SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2587 timezone 2588-------------------------- 2589 Sun Oct 26 01:00:00 2014 2590(1 row) 2591 2592SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2593 timezone 2594-------------------------- 2595 Sun Oct 26 01:59:59 2014 2596(1 row) 2597 2598SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2599 timezone 2600-------------------------- 2601 Sun Oct 26 01:00:00 2014 2602(1 row) 2603 2604SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2605 timezone 2606-------------------------- 2607 Sun Oct 26 01:00:01 2014 2608(1 row) 2609 2610SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; 2611 timezone 2612-------------------------- 2613 Sun Oct 26 02:00:00 2014 2614(1 row) 2615 2616SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2617 timezone 2618-------------------------- 2619 Sun Mar 27 00:00:00 2011 2620(1 row) 2621 2622SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2623 timezone 2624-------------------------- 2625 Sun Mar 27 01:00:00 2011 2626(1 row) 2627 2628SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; 2629 timezone 2630-------------------------- 2631 Sun Mar 27 01:59:59 2011 2632(1 row) 2633 2634SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2635 timezone 2636-------------------------- 2637 Sun Mar 27 03:00:00 2011 2638(1 row) 2639 2640SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK'; 2641 timezone 2642-------------------------- 2643 Sun Mar 27 03:00:01 2011 2644(1 row) 2645 2646SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; 2647 timezone 2648-------------------------- 2649 Sun Mar 27 03:59:59 2011 2650(1 row) 2651 2652SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2653 timezone 2654-------------------------- 2655 Sun Mar 27 04:00:00 2011 2656(1 row) 2657 2658SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2659 timezone 2660-------------------------- 2661 Sun Oct 26 01:00:00 2014 2662(1 row) 2663 2664SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; 2665 timezone 2666-------------------------- 2667 Sun Oct 26 01:59:59 2014 2668(1 row) 2669 2670SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2671 timezone 2672-------------------------- 2673 Sun Oct 26 01:00:00 2014 2674(1 row) 2675 2676SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK'; 2677 timezone 2678-------------------------- 2679 Sun Oct 26 01:00:01 2014 2680(1 row) 2681 2682SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; 2683 timezone 2684-------------------------- 2685 Sun Oct 26 02:00:00 2014 2686(1 row) 2687 2688-- 2689-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504) 2690-- 2691create temp table tmptz (f1 timestamptz primary key); 2692insert into tmptz values ('2017-01-18 00:00+00'); 2693explain (costs off) 2694select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; 2695 QUERY PLAN 2696------------------------------------------------------------------------------------------------- 2697 Seq Scan on tmptz 2698 Filter: (timezone('utc'::text, f1) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone) 2699(2 rows) 2700 2701select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; 2702 f1 2703------------------------------ 2704 Tue Jan 17 16:00:00 2017 PST 2705(1 row) 2706 2707