def test_converter_to_snowflake_bindings_error():
converter = SnowflakeConverter()
with pytest.raises(
ProgrammingError,
match=r"Binding data in type \(somethingsomething\) is not supported",
):
converter._somethingsomething_to_snowflake_bindings("Bogus")
def test_fetch_timestamps(conn_cnx):
PST_TZ = "America/Los_Angeles"
tzdiff = 1860 - 1440 # -07:00
tzinfo = SnowflakeConverter._generate_tzinfo_from_tzoffset(tzdiff)
# TIMESTAMP_TZ
r0 = _compose_tz('1325568896.123456', tzinfo)
r1 = _compose_tz('1325568896.123456', tzinfo)
r2 = _compose_tz('1325568896.123456', tzinfo)
r3 = _compose_tz('1325568896.123456', tzinfo)
r4 = _compose_tz('1325568896.12345', tzinfo)
r5 = _compose_tz('1325568896.1234', tzinfo)
r6 = _compose_tz('1325568896.123', tzinfo)
r7 = _compose_tz('1325568896.12', tzinfo)
r8 = _compose_tz('1325568896.1', tzinfo)
r9 = _compose_tz('1325568896', tzinfo)
# TIMESTAMP_NTZ
r10 = _compose_ntz('1325568896.123456')
r11 = _compose_ntz('1325568896.123456')
r12 = _compose_ntz('1325568896.123456')
r13 = _compose_ntz('1325568896.123456')
r14 = _compose_ntz('1325568896.12345')
r15 = _compose_ntz('1325568896.1234')
r16 = _compose_ntz('1325568896.123')
r17 = _compose_ntz('1325568896.12')
r18 = _compose_ntz('1325568896.1')
r19 = _compose_ntz('1325568896')
# TIMESTAMP_LTZ
r20 = _compose_ltz('1325568896.123456', PST_TZ)
r21 = _compose_ltz('1325568896.123456', PST_TZ)
r22 = _compose_ltz('1325568896.123456', PST_TZ)
r23 = _compose_ltz('1325568896.123456', PST_TZ)
r24 = _compose_ltz('1325568896.12345', PST_TZ)
r25 = _compose_ltz('1325568896.1234', PST_TZ)
r26 = _compose_ltz('1325568896.123', PST_TZ)
r27 = _compose_ltz('1325568896.12', PST_TZ)
r28 = _compose_ltz('1325568896.1', PST_TZ)
r29 = _compose_ltz('1325568896', PST_TZ)
# TIME
r30 = time(5, 7, 8, 123456)
r31 = time(5, 7, 8, 123456)
r32 = time(5, 7, 8, 123456)
r33 = time(5, 7, 8, 123456)
r34 = time(5, 7, 8, 123450)
r35 = time(5, 7, 8, 123400)
r36 = time(5, 7, 8, 123000)
r37 = time(5, 7, 8, 120000)
r38 = time(5, 7, 8, 100000)
r39 = time(5, 7, 8)
with conn_cnx() as cnx:
cur = cnx.cursor()
cur.execute("""
ALTER SESSION SET TIMEZONE='{tz}';
""".format(tz=PST_TZ))
cur.execute("""
SELECT
'2012-01-03 12:34:56.123456789+07:00'::timestamp_tz(9),
'2012-01-03 12:34:56.12345678+07:00'::timestamp_tz(8),
'2012-01-03 12:34:56.1234567+07:00'::timestamp_tz(7),
'2012-01-03 12:34:56.123456+07:00'::timestamp_tz(6),
'2012-01-03 12:34:56.12345+07:00'::timestamp_tz(5),
'2012-01-03 12:34:56.1234+07:00'::timestamp_tz(4),
'2012-01-03 12:34:56.123+07:00'::timestamp_tz(3),
'2012-01-03 12:34:56.12+07:00'::timestamp_tz(2),
'2012-01-03 12:34:56.1+07:00'::timestamp_tz(1),
'2012-01-03 12:34:56+07:00'::timestamp_tz(0),
'2012-01-03 05:34:56.123456789'::timestamp_ntz(9),
'2012-01-03 05:34:56.12345678'::timestamp_ntz(8),
'2012-01-03 05:34:56.1234567'::timestamp_ntz(7),
'2012-01-03 05:34:56.123456'::timestamp_ntz(6),
'2012-01-03 05:34:56.12345'::timestamp_ntz(5),
'2012-01-03 05:34:56.1234'::timestamp_ntz(4),
'2012-01-03 05:34:56.123'::timestamp_ntz(3),
'2012-01-03 05:34:56.12'::timestamp_ntz(2),
'2012-01-03 05:34:56.1'::timestamp_ntz(1),
'2012-01-03 05:34:56'::timestamp_ntz(0),
'2012-01-02 21:34:56.123456789'::timestamp_ltz(9),
'2012-01-02 21:34:56.12345678'::timestamp_ltz(8),
'2012-01-02 21:34:56.1234567'::timestamp_ltz(7),
'2012-01-02 21:34:56.123456'::timestamp_ltz(6),
'2012-01-02 21:34:56.12345'::timestamp_ltz(5),
'2012-01-02 21:34:56.1234'::timestamp_ltz(4),
'2012-01-02 21:34:56.123'::timestamp_ltz(3),
'2012-01-02 21:34:56.12'::timestamp_ltz(2),
'2012-01-02 21:34:56.1'::timestamp_ltz(1),
'2012-01-02 21:34:56'::timestamp_ltz(0),
'05:07:08.123456789'::time(9),
'05:07:08.12345678'::time(8),
'05:07:08.1234567'::time(7),
'05:07:08.123456'::time(6),
'05:07:08.12345'::time(5),
'05:07:08.1234'::time(4),
'05:07:08.123'::time(3),
'05:07:08.12'::time(2),
'05:07:08.1'::time(1),
'05:07:08'::time(0)
""")
ret = cur.fetchone()
assert ret[0] == r0
assert ret[1] == r1
assert ret[2] == r2
assert ret[3] == r3
assert ret[4] == r4
assert ret[5] == r5
assert ret[6] == r6
assert ret[7] == r7
assert ret[8] == r8
assert ret[9] == r9
assert ret[10] == r10
assert ret[11] == r11
assert ret[12] == r12
assert ret[13] == r13
assert ret[14] == r14
assert ret[15] == r15
assert ret[16] == r16
assert ret[17] == r17
assert ret[18] == r18
assert ret[19] == r19
assert ret[20] == r20
assert ret[21] == r21
assert ret[22] == r22
assert ret[23] == r23
assert ret[24] == r24
assert ret[25] == r25
assert ret[26] == r26
assert ret[27] == r27
assert ret[28] == r28
assert ret[29] == r29
assert ret[30] == r30
assert ret[31] == r31
assert ret[32] == r32
assert ret[33] == r33
assert ret[34] == r34
assert ret[35] == r35
assert ret[36] == r36
assert ret[37] == r37
assert ret[38] == r38
assert ret[39] == r39
def test_converter_to_snowflake_error():
converter = SnowflakeConverter()
with pytest.raises(ProgrammingError, match=r'Binding data in type \(bogus\) is not supported'):
converter._bogus_to_snowflake('Bogus')
def test_fetch_various_timestamps(conn_cnx):
"""
More coverage of timestamp
Currently TIMESTAMP_LTZ is not tested.
"""
PST_TZ = "America/Los_Angeles"
epoch_times = ['1325568896', '-2208943503', '0', '-1']
timezones = ['+07:00', '+00:00', '-01:00', '-09:00']
fractions = '123456789'
data_types = ['TIMESTAMP_TZ', 'TIMESTAMP_NTZ']
data = []
for dt in data_types:
for et in epoch_times:
if dt == 'TIMESTAMP_TZ':
for tz in timezones:
tzdiff = (int(tz[1:3]) * 60 +
int(tz[4:6])) * (-1 if tz[0] == '-' else 1)
tzinfo = SnowflakeConverter._generate_tzinfo_from_tzoffset(
tzdiff)
ts = datetime.fromtimestamp(float(et), tz=tzinfo)
data.append({
'scale':
0,
'dt':
dt,
'inp':
ts.strftime('%Y-%m-%d %H:%M:%S{tz}'.format(tz=tz)),
'out':
ts
})
for idx in range(len(fractions)):
scale = idx + 1
if idx + 1 != 6: # SNOW-28597
ts0 = datetime.fromtimestamp(float(et), tz=tzinfo)
ts0_str = ts0.strftime(
'%Y-%m-%d %H:%M:%S.{ff}{tz}'.format(
ff=fractions[:idx + 1], tz=tz))
ts1 = parse(ts0_str)
data.append({
'scale': scale,
'dt': dt,
'inp': ts0_str,
'out': ts1
})
elif dt == 'TIMESTAMP_LTZ':
# WIP. this test work in edge case
tzinfo = pytz.timezone(PST_TZ)
ts0 = datetime.fromtimestamp(float(et))
ts0 = pytz.utc.localize(ts0, is_dst=False).astimezone(tzinfo)
ts0_str = ts0.strftime('%Y-%m-%d %H:%M:%S')
ts1 = ts0
data.append({'scale': 0, 'dt': dt, 'inp': ts0_str, 'out': ts1})
for idx in range(len(fractions)):
ts0 = datetime.fromtimestamp(float(et))
ts0 = pytz.utc.localize(ts0,
is_dst=False).astimezone(tzinfo)
ts0_str = ts0.strftime('%Y-%m-%d %H:%M:%S.{ff}'.format(
ff=fractions[:idx + 1]))
ts1 = ts0 + timedelta(
seconds=float('0.{0}'.format(fractions[:idx + 1])))
data.append({
'scale': idx + 1,
'dt': dt,
'inp': ts0_str,
'out': ts1
})
else:
ts0_str = datetime.fromtimestamp(
float(et)).strftime('%Y-%m-%d %H:%M:%S')
ts1 = parse(ts0_str)
data.append({'scale': 0, 'dt': dt, 'inp': ts0_str, 'out': ts1})
for idx in range(len(fractions)):
ts0_str = datetime.fromtimestamp(float(et)).strftime(
'%Y-%m-%d %H:%M:%S.{ff}'.format(ff=fractions[:idx +
1]))
ts1 = parse(ts0_str)
data.append({
'scale': idx + 1,
'dt': dt,
'inp': ts0_str,
'out': ts1
})
sql = "SELECT "
for d in data:
sql += "'{inp}'::{dt}({scale}), ".format(inp=d['inp'],
dt=d['dt'],
scale=d['scale'])
sql += "1"
with conn_cnx() as cnx:
cur = cnx.cursor()
cur.execute("""
ALTER SESSION SET TIMEZONE='{tz}';
""".format(tz=PST_TZ))
rec = cur.execute(sql).fetchone()
for idx, d in enumerate(data):
comp, lower, higher = _in_range(d['out'], rec[idx])
assert comp, 'data: {d}: target={target}, lower={lower}, higher={' \
'higher}'.format(
d=d, target=rec[idx], lower=lower, higher=higher)
