2709
2709
-- to_timestamp()
2711
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
2712
to_timestamp_1 | to_timestamp
2713
----------------+------------------------------
2714
| Sat Feb 16 08:14:30 0097 PST
2717
SELECT '' AS to_timestamp_2, to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
2718
to_timestamp_2 | to_timestamp
2719
----------------+------------------------------
2720
| Sat Feb 16 08:14:30 0097 PST
2723
SELECT '' AS to_timestamp_3, to_timestamp('1985 January 12', 'YYYY FMMonth DD');
2724
to_timestamp_3 | to_timestamp
2725
----------------+------------------------------
2726
| Sat Jan 12 00:00:00 1985 PST
2729
SELECT '' AS to_timestamp_4, to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
2730
'"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
2731
to_timestamp_4 | to_timestamp
2732
----------------+------------------------------
2733
| Sun May 16 00:00:00 1976 PDT
2736
SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
2737
to_timestamp_5 | to_timestamp
2738
----------------+------------------------------
2739
| Sat Aug 21 00:00:00 1582 PST
2742
SELECT '' AS to_timestamp_6, to_timestamp('15 "text between quote marks" 98 54 45',
2743
E'HH24 "\\text between quote marks\\"" YY MI SS');
2744
to_timestamp_6 | to_timestamp
2745
----------------+------------------------------
2746
| Thu Jan 01 15:54:45 1998 PST
2749
SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
2750
to_timestamp_7 | to_timestamp
2751
----------------+------------------------------
2752
| Fri May 12 14:45:48 2000 PDT
2755
SELECT '' AS to_timestamp_8, to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
2756
to_timestamp_8 | to_timestamp
2757
----------------+------------------------------
2758
| Sun Jan 09 00:00:00 2000 PST
2761
SELECT '' AS to_timestamp_9, to_timestamp('97/Feb/16', 'YYMonDD');
2711
SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
2713
------------------------------
2714
Sat Feb 16 08:14:30 0097 PST
2717
SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
2719
------------------------------
2720
Sat Feb 16 08:14:30 0097 PST
2723
SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
2725
------------------------------
2726
Sat Jan 12 00:00:00 1985 PST
2729
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
2730
'"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
2732
------------------------------
2733
Sun May 16 00:00:00 1976 PDT
2736
SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
2738
------------------------------
2739
Sat Aug 21 00:00:00 1582 PST
2742
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
2743
E'HH24 "\\text between quote marks\\"" YY MI SS');
2745
------------------------------
2746
Thu Jan 01 15:54:45 1998 PST
2749
SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
2751
------------------------------
2752
Fri May 12 14:45:48 2000 PDT
2755
SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
2757
------------------------------
2758
Sun Jan 09 00:00:00 2000 PST
2761
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
2762
2762
ERROR: invalid value "/Fe" for "Mon"
2763
2763
DETAIL: The given value did not match any of the allowed values for this field.
2764
SELECT '' AS to_timestamp_10, to_timestamp('19971116', 'YYYYMMDD');
2765
to_timestamp_10 | to_timestamp
2766
-----------------+------------------------------
2767
| Sun Nov 16 00:00:00 1997 PST
2770
SELECT '' AS to_timestamp_11, to_timestamp('20000-1116', 'YYYY-MMDD');
2771
to_timestamp_11 | to_timestamp
2772
-----------------+-------------------------------
2773
| Thu Nov 16 00:00:00 20000 PST
2776
SELECT '' AS to_timestamp_12, to_timestamp('9-1116', 'Y-MMDD');
2777
to_timestamp_12 | to_timestamp
2778
-----------------+------------------------------
2779
| Mon Nov 16 00:00:00 2009 PST
2782
SELECT '' AS to_timestamp_13, to_timestamp('95-1116', 'YY-MMDD');
2783
to_timestamp_13 | to_timestamp
2784
-----------------+------------------------------
2785
| Thu Nov 16 00:00:00 1995 PST
2788
SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
2789
to_timestamp_14 | to_timestamp
2790
-----------------+------------------------------
2791
| Thu Nov 16 00:00:00 1995 PST
2794
SELECT '' AS to_timestamp_15, to_timestamp('2005426', 'YYYYWWD');
2795
to_timestamp_15 | to_timestamp
2796
-----------------+------------------------------
2797
| Sat Oct 15 00:00:00 2005 PDT
2800
SELECT '' AS to_timestamp_16, to_timestamp('2005300', 'YYYYDDD');
2801
to_timestamp_16 | to_timestamp
2802
-----------------+------------------------------
2803
| Thu Oct 27 00:00:00 2005 PDT
2806
SELECT '' AS to_timestamp_17, to_timestamp('2005527', 'IYYYIWID');
2807
to_timestamp_17 | to_timestamp
2808
-----------------+------------------------------
2809
| Sun Jan 01 00:00:00 2006 PST
2812
SELECT '' AS to_timestamp_18, to_timestamp('005527', 'IYYIWID');
2813
to_timestamp_18 | to_timestamp
2814
-----------------+------------------------------
2815
| Sun Jan 01 00:00:00 2006 PST
2818
SELECT '' AS to_timestamp_19, to_timestamp('05527', 'IYIWID');
2819
to_timestamp_19 | to_timestamp
2820
-----------------+------------------------------
2821
| Sun Jan 01 00:00:00 2006 PST
2824
SELECT '' AS to_timestamp_20, to_timestamp('5527', 'IIWID');
2825
to_timestamp_20 | to_timestamp
2826
-----------------+------------------------------
2827
| Sun Jan 01 00:00:00 2006 PST
2830
SELECT '' AS to_timestamp_21, to_timestamp('2005364', 'IYYYIDDD');
2831
to_timestamp_21 | to_timestamp
2832
-----------------+------------------------------
2833
| Sun Jan 01 00:00:00 2006 PST
2764
SELECT to_timestamp('19971116', 'YYYYMMDD');
2766
------------------------------
2767
Sun Nov 16 00:00:00 1997 PST
2770
SELECT to_timestamp('20000-1116', 'YYYY-MMDD');
2772
-------------------------------
2773
Thu Nov 16 00:00:00 20000 PST
2776
SELECT to_timestamp('9-1116', 'Y-MMDD');
2778
------------------------------
2779
Mon Nov 16 00:00:00 2009 PST
2782
SELECT to_timestamp('95-1116', 'YY-MMDD');
2784
------------------------------
2785
Thu Nov 16 00:00:00 1995 PST
2788
SELECT to_timestamp('995-1116', 'YYY-MMDD');
2790
------------------------------
2791
Thu Nov 16 00:00:00 1995 PST
2794
SELECT to_timestamp('2005426', 'YYYYWWD');
2796
------------------------------
2797
Sat Oct 15 00:00:00 2005 PDT
2800
SELECT to_timestamp('2005300', 'YYYYDDD');
2802
------------------------------
2803
Thu Oct 27 00:00:00 2005 PDT
2806
SELECT to_timestamp('2005527', 'IYYYIWID');
2808
------------------------------
2809
Sun Jan 01 00:00:00 2006 PST
2812
SELECT to_timestamp('005527', 'IYYIWID');
2814
------------------------------
2815
Sun Jan 01 00:00:00 2006 PST
2818
SELECT to_timestamp('05527', 'IYIWID');
2820
------------------------------
2821
Sun Jan 01 00:00:00 2006 PST
2824
SELECT to_timestamp('5527', 'IIWID');
2826
------------------------------
2827
Sun Jan 01 00:00:00 2006 PST
2830
SELECT to_timestamp('2005364', 'IYYYIDDD');
2832
------------------------------
2833
Sun Jan 01 00:00:00 2006 PST
2836
SELECT to_timestamp('20050302', 'YYYYMMDD');
2838
------------------------------
2839
Wed Mar 02 00:00:00 2005 PST
2842
SELECT to_timestamp('2005 03 02', 'YYYYMMDD');
2844
------------------------------
2845
Wed Mar 02 00:00:00 2005 PST
2848
SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD');
2850
------------------------------
2851
Wed Mar 02 00:00:00 2005 PST
2854
SELECT to_timestamp(' 20050302', 'YYYYMMDD');
2856
------------------------------
2857
Wed Mar 02 00:00:00 2005 PST
2837
2861
-- Check errors for some incorrect usages of to_timestamp()
2839
2863
-- Mixture of date conventions (ISO week and Gregorian):
2840
SELECT '' AS to_timestamp_22, to_timestamp('2005527', 'YYYYIWID');
2864
SELECT to_timestamp('2005527', 'YYYYIWID');
2841
2865
ERROR: invalid combination of date conventions
2842
2866
HINT: Do not mix Gregorian and ISO week date conventions in a formatting template.
2843
2867
-- Insufficient characters in the source string:
2844
SELECT '' AS to_timestamp_23, to_timestamp('19971', 'YYYYMMDD');
2868
SELECT to_timestamp('19971', 'YYYYMMDD');
2845
2869
ERROR: source string too short for "MM" formatting field
2846
2870
DETAIL: Field requires 2 characters, but only 1 remain.
2847
2871
HINT: If your source string is not fixed-width, try using the "FM" modifier.
2848
2872
-- Insufficient digit characters for a single node:
2849
SELECT '' AS to_timestamp_24, to_timestamp('19971)24', 'YYYYMMDD');
2873
SELECT to_timestamp('19971)24', 'YYYYMMDD');
2850
2874
ERROR: invalid value "1)" for "MM"
2851
2875
DETAIL: Field requires 2 characters, but only 1 could be parsed.
2852
2876
HINT: If your source string is not fixed-width, try using the "FM" modifier.
2853
2877
-- Value clobbering:
2854
SELECT '' AS to_timestamp_25, to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD');
2878
SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD');
2855
2879
ERROR: conflicting values for "Mon" field in formatting string
2856
2880
DETAIL: This value contradicts a previous setting for the same field type.
2857
2881
-- Non-numeric input:
2858
SELECT '' AS to_timestamp_26, to_timestamp('199711xy', 'YYYYMMDD');
2882
SELECT to_timestamp('199711xy', 'YYYYMMDD');
2859
2883
ERROR: invalid value "xy" for "DD"
2860
2884
DETAIL: Value must be an integer.
2861
2885
-- Input that doesn't fit in an int:
2862
SELECT '' AS to_timestamp_27, to_timestamp('10000000000', 'FMYYYY');
2886
SELECT to_timestamp('10000000000', 'FMYYYY');
2863
2887
ERROR: value for "YYYY" in source string is out of range
2864
2888
DETAIL: Value must be in the range -2147483648 to 2147483647.