snowflake-connector-python/test/test_snowsql_timestamp_format.py at master · byule/snowflake-connector-python · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Copyright (c) 2012-2019 Snowflake Computing Inc. All right reserved.
#
import pytest

from snowflake.connector.compat import (IS_WINDOWS, PY2, PY34_EXACT)
from snowflake.connector.converter_snowsql import SnowflakeConverterSnowSQL


@pytest.mark.skipif(
    IS_WINDOWS or PY2 or PY34_EXACT,
    reason="SnowSQL runs on Python 35+. "
           "Windows doesn't support more than 9999 yeers")
def test_snowsql_timestamp_format(conn_cnx):
    """
    In SnowSQL, OverflowError should not happen
    """
    with conn_cnx(
            converter_class=SnowflakeConverterSnowSQL
    ) as cnx:
        cnx.cursor().execute("""
ALTER SESSION SET
    TIMEZONE='America/Los_Angeles',
    TIMESTAMP_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM',
    TIMESTAMP_NTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM',
    TIMESTAMP_LTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM';
""")
        ret = cnx.cursor().execute("""
SELECT
    '19999-09-30 12:34:56'::timestamp_ltz,
    '19999-09-30 12:34:56'::timestamp_ntz,
    '2001-09-30 12:34:56.00123400'::timestamp_ntz(8)
""").fetchone()
        assert ret[0] == 'Thu, 30 Sep 19999 19:34:56 +0000'
        assert ret[1] == 'Thu, 30 Sep 19999 12:34:56 '

        # The last space is included as TZHTZM is an empty value if
        # datatype is datetime.
        assert ret[2] == 'Sun, 30 Sep 2001 12:34:56 '

        # NOTE timestamp_tz doesn't accept the timestamp out of range
        # what is the range?


@pytest.mark.skipif(PY2 or PY34_EXACT, reason="SnowSQL runs on Python35+")
def test_snowsql_timestamp_negative_epoch(conn_cnx):
    with conn_cnx(converter_class=SnowflakeConverterSnowSQL) as cnx:
        cnx.cursor().execute("""
ALTER SESSION SET
    TIMEZONE='America/Los_Angeles',
    TIMESTAMP_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM',
    TIMESTAMP_NTZ_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM',
    TIMESTAMP_LTZ_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM';
""")
        ret = cnx.cursor().execute("""
    SELECT
        '1969-09-30 12:34:56.123456789'::timestamp_ltz(7),
        '1969-09-30 12:34:56.123456789'::timestamp_ntz(8),
        '1969-09-30 12:34:56.123456789 -08:00'::timestamp_tz(8),
        '1969-09-30 12:34:56.123456789 -08:00'::timestamp_tz(4),
        '2001-09-30 12:34:56.00123400'::timestamp_ntz(8)
    """).fetchone()
        assert ret[0] == '1969-09-30 12:34:56.123456700 -0700'
        assert ret[1] == '1969-09-30 12:34:56.123456780 '
        assert ret[2] == '1969-09-30 12:34:56.123456780 -0800'
        assert ret[3] == '1969-09-30 12:34:56.123400000 -0800'
        # a scale in format forces to add 0 to the end
        assert ret[4] == '2001-09-30 12:34:56.001234000 '
        cnx.cursor().execute("""
ALTER SESSION SET
    TIMEZONE='America/Los_Angeles',
    TIMESTAMP_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM',
    TIMESTAMP_NTZ_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM',
    TIMESTAMP_LTZ_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';
""")
        ret = cnx.cursor().execute("""
    SELECT
        '1969-09-30 12:34:56.123456789'::timestamp_ltz(7),
        '1969-09-30 12:34:56.123456789'::timestamp_ntz(8),
        '1969-09-30 12:34:56.123456789 -08:00'::timestamp_tz(8),
        '1969-09-30 12:34:56.123456789 -08:00'::timestamp_tz(4),
        '2001-09-30 12:34:56.00123400'::timestamp_ntz(8)
    """).fetchone()
        assert ret[0] == '1969-09-30 12:34:56.1234567 -0700'
        assert ret[1] == '1969-09-30 12:34:56.12345678 '
        assert ret[2] == '1969-09-30 12:34:56.12345678 -0800'
        assert ret[3] == '1969-09-30 12:34:56.1234 -0800'
        assert ret[4] == '2001-09-30 12:34:56.00123400 '