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)