def expected_data_transform_tz_impl(data, scale=_scale):
timezone = data['timezone']
tzinfo = _generate_tzinfo_from_tzoffset(timezone - 1440)
epoch = data['epoch']
if scale > 3:
frac = data['fraction']
if epoch < 0:
epoch += 1
frac = 10**9 - frac
frac = str(int(frac / 10**(9 - scale)))
ZERO_FILL = '000000000'
frac = ZERO_FILL[:scale - len(frac)] + frac
epoch = int(str(epoch) + frac)
microsec = str(epoch)
if scale > 6:
microsec = microsec[:-scale] + "." + microsec[-scale:-scale +
6]
else:
microsec = microsec[:-scale] + "." + microsec[
-scale:] if scale else microsec
if platform.system() == 'Windows':
t = datetime.datetime.utcfromtimestamp(0) + datetime.timedelta(
seconds=(float(microsec)))
if pytz.utc != tzinfo:
t += tzinfo.utcoffset(t)
return t.replace(tzinfo=tzinfo)
else:
return datetime.datetime.fromtimestamp(float(microsec),
tz=tzinfo)
def test_fetch_various_timestamps(conn_cnx):
"""More coverage of timestamp.
Notes:
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 = _generate_tzinfo_from_tzoffset(tzdiff)
try:
ts = datetime.fromtimestamp(float(et), tz=tzinfo)
except (OSError, ValueError):
ts = ZERO_EPOCH + timedelta(seconds=float(et))
if pytz.utc != tzinfo:
ts += tzinfo.utcoffset(ts)
ts = ts.replace(tzinfo=tzinfo)
data.append({
"scale": 0,
"dt": dt,
"inp": ts.strftime(f"%Y-%m-%d %H:%M:%S{tz}"),
"out": ts,
})
for idx in range(len(fractions)):
scale = idx + 1
if idx + 1 != 6: # SNOW-28597
try:
ts0 = datetime.fromtimestamp(float(et),
tz=tzinfo)
except (OSError, ValueError):
ts0 = ZERO_EPOCH + timedelta(seconds=float(et))
if pytz.utc != tzinfo:
ts0 += tzinfo.utcoffset(ts0)
ts0 = ts0.replace(tzinfo=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).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).astimezone(tzinfo)
ts0_str = ts0.strftime(
f"%Y-%m-%d %H:%M:%S.{fractions[: idx + 1]}")
ts1 = ts0 + timedelta(
seconds=float(f"0.{fractions[: idx + 1]}"))
data.append({
"scale": idx + 1,
"dt": dt,
"inp": ts0_str,
"out": ts1
})
else:
# TIMESTAMP_NTZ
try:
ts0 = datetime.fromtimestamp(float(et))
except (OSError, ValueError):
ts0 = ZERO_EPOCH + timedelta(seconds=(float(et)))
ts0_str = ts0.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)):
try:
ts0 = datetime.fromtimestamp(float(et))
except (OSError, ValueError):
ts0 = ZERO_EPOCH + timedelta(seconds=(float(et)))
ts0_str = ts0.strftime(
f"%Y-%m-%d %H:%M:%S.{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)
def test_fetch_timestamps(conn_cnx):
PST_TZ = "America/Los_Angeles"
tzdiff = 1860 - 1440 # -07:00
tzinfo = _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_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 = _generate_tzinfo_from_tzoffset(tzdiff)
try:
ts = datetime.fromtimestamp(float(et), tz=tzinfo)
except (OSError, ValueError):
ts = ZERO_EPOCH + timedelta(seconds=float(et))
if pytz.utc != tzinfo:
ts += tzinfo.utcoffset(ts)
ts = ts.replace(tzinfo=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
try:
ts0 = datetime.fromtimestamp(float(et),
tz=tzinfo)
except (OSError, ValueError):
ts0 = ZERO_EPOCH + timedelta(seconds=float(et))
if pytz.utc != tzinfo:
ts0 += tzinfo.utcoffset(ts0)
ts0 = ts0.replace(tzinfo=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).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).astimezone(tzinfo)
ts0_str = ts0.strftime(
'%Y-%m-%d %H:%M:%S.{ff}'.format(
ff=fractions[:idx + 1]
))
ts1 = ts0 + timedelta(seconds=float(
'0.{}'.format(fractions[:idx + 1])))
data.append({
'scale': idx + 1,
'dt': dt,
'inp': ts0_str,
'out': ts1
})
else:
# TIMESTAMP_NTZ
try:
ts0 = datetime.fromtimestamp(float(et))
except (OSError, ValueError):
ts0 = ZERO_EPOCH + timedelta(seconds=(float(et)))
ts0_str = ts0.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)):
try:
ts0 = datetime.fromtimestamp(float(et))
except (OSError, ValueError):
ts0 = ZERO_EPOCH + timedelta(seconds=(float(et)))
ts0_str = ts0.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)
