def test_column_use_server_timezone(self):
# Insert datetime with no timezone
# into column with timezone Asia/Novosibirsk
# using server's timezone (Europe/Moscow)
with patch_env_tz('Europe/Moscow'):
with self.create_table(self.table_columns(with_tz=True)):
self.client.execute(
'INSERT INTO test (a) VALUES', [self.dt_arr], columnar=True
)
self.emit_cli(
"INSERT INTO test (a) VALUES ('2017-07-14 05:40:00')"
)
query = 'SELECT toInt32(a) FROM test'
inserted = self.emit_cli(query)
# 1499985600 = 1500000000 - 4 * 3600
self.assertEqual(inserted, '1499985600\n1499985600\n')
query = 'SELECT * FROM test'
inserted = self.emit_cli(query)
self.assertEqual(
inserted,
'2017-07-14 05:40:00\n2017-07-14 05:40:00\n'
)
inserted = self.client.execute(query, columnar=True)
self.assertArraysEqual(
inserted[0],
self.make_tz_numpy_array(self.dt, self.col_tz_name)
)
def test_datetime_with_timezone_column_use_server_timezone(self):
# Insert datetime with timezone Asia/Kamchatka
# into column with timezone Asia/Novosibirsk
# using server's timezone (Europe/Moscow)
with patch_env_tz('Europe/Moscow'):
with self.create_table(self.table_columns(with_tz=True)):
self.client.execute(
'INSERT INTO test (a) VALUES', [self.dt_tz], columnar=True
)
self.emit_cli(
"INSERT INTO test (a) VALUES "
"(toDateTime('2017-07-14 05:40:00', 'Asia/Kamchatka'))",
)
query = 'SELECT toInt32(a) FROM test'
inserted = self.emit_cli(query)
# 1499967600 = 1500000000 - 12 * 3600
self.assertEqual(inserted, '1499967600\n1499967600\n')
query = 'SELECT * FROM test'
inserted = self.emit_cli(query)
# 2017-07-14 00:40:00 = 2017-07-14 05:40:00 - 05:00:00
# (Kamchatka - Novosibirsk)
self.assertEqual(
inserted,
'2017-07-14 00:40:00\n2017-07-14 00:40:00\n'
)
inserted = self.client.execute(query, columnar=True)
dt = datetime(2017, 7, 14, 0, 40)
self.assertArraysEqual(
inserted[0], self.make_tz_numpy_array(dt, self.col_tz_name)
)
def test_datetime_with_timezone_use_server_timezone(self):
# Insert datetime with timezone Asia/Kamchatka
# into column with no timezone
# using server's timezone (Europe/Moscow)
server_tz_name = self.client.execute('SELECT timezone()')[0][0]
offset = timezone(server_tz_name).utcoffset(self.dt)
with patch_env_tz('Asia/Novosibirsk'):
with self.create_table(self.table_columns()):
self.client.execute(
'INSERT INTO test (a) VALUES', [self.dt_tz], columnar=True
)
self.emit_cli(
"INSERT INTO test (a) VALUES "
"(toDateTime('2017-07-14 05:40:00', 'Asia/Kamchatka'))",
)
query = 'SELECT toInt32(a) FROM test'
inserted = self.emit_cli(query)
# 1499967600 = 1500000000 - 12 * 3600
self.assertEqual(inserted, '1499967600\n1499967600\n')
query = 'SELECT * FROM test'
inserted = self.emit_cli(query)
dt = (self.dt_tz.to_pydatetime()[0].astimezone(utc) + offset) \
.replace(tzinfo=None)
self.assertEqual(inserted, '{dt}\n{dt}\n'.format(dt=dt))
inserted = self.client.execute(query, columnar=True)
self.assertArraysEqual(
inserted[0], self.make_numpy_d64ns([dt.isoformat()] * 2)
)
def test_use_server_timezone(self):
# Insert datetime with timezone UTC
# into column with no timezone
# using server's timezone (Europe/Moscow)
# Determine server timezone and calculate expected timestamp.
server_tz_name = self.client.execute('SELECT timezone()')[0][0]
offset = timezone(server_tz_name).utcoffset(self.dt).total_seconds()
timestamp = 1500010800 - int(offset)
with patch_env_tz('Asia/Novosibirsk'):
with self.create_table(self.table_columns()):
self.client.execute(
'INSERT INTO test (a) VALUES', [self.dt_arr], columnar=True
)
self.emit_cli(
"INSERT INTO test (a) VALUES ('2017-07-14 05:40:00')"
)
query = 'SELECT toInt32(a) FROM test'
inserted = self.emit_cli(query)
self.assertEqual(inserted, '{ts}\n{ts}\n'.format(ts=timestamp))
query = 'SELECT * FROM test'
inserted = self.emit_cli(query)
self.assertEqual(
inserted,
'2017-07-14 05:40:00\n2017-07-14 05:40:00\n'
)
inserted = self.client.execute(query, columnar=True)
self.assertArraysEqual(
inserted[0], self.make_numpy_d64ns([self.dt_str] * 2)
)
def test_datetime_with_timezone(self):
dt = datetime(2017, 7, 14, 5, 40, 0)
params = {'x': timezone('Asia/Kamchatka').localize(dt)}
self.assert_subst(self.single_tpl, params,
"SELECT '2017-07-13 20:40:00'")
tpl = ('SELECT toDateTime(toInt32(toDateTime(%(x)s))), '
'toInt32(toDateTime(%(x)s))')
with patch_env_tz('Asia/Novosibirsk'):
# use server timezone
rv = self.client.execute(tpl,
params,
settings={'use_client_time_zone': False})
self.assertEqual(rv,
[(datetime(2017, 7, 13, 20, 40, 0), 1499967600)])
query = (
"SELECT "
"toDateTime(toInt32(toDateTime('{0}', 'Asia/Kamchatka'))), "
"toInt32(toDateTime('{0}', 'Asia/Kamchatka'))"
).format('2017-07-14 05:40:00')
rv = self.emit_cli(query, use_client_time_zone=0)
self.assertEqual(rv, '2017-07-13 20:40:00\t1499967600\n')
# use client timezone
rv = self.client.execute(tpl,
params,
settings={'use_client_time_zone': True})
self.assertEqual(rv,
[(datetime(2017, 7, 14, 0, 40, 0), 1499967600)])
query = (
"SELECT "
"toDateTime(toInt32(toDateTime('{0}', 'Asia/Kamchatka'))), "
"toInt32(toDateTime('{0}', 'Asia/Kamchatka'))"
).format('2017-07-14 05:40:00')
rv = self.emit_cli(query, use_client_time_zone=1)
self.assertEqual(rv, '2017-07-14 00:40:00\t1499967600\n')
def test_datetime_with_timezone_use_client_timezone(self):
# Insert datetime with timezone Asia/Kamchatka
# into column with no timezone
# using client's timezone Asia/Novosibirsk
settings = {'use_client_time_zone': True}
with patch_env_tz('Asia/Novosibirsk'):
with self.create_table(self.table_columns()):
self.client.execute(
'INSERT INTO test (a) VALUES', [self.dt_tz],
settings=settings, columnar=True
)
self.emit_cli(
"INSERT INTO test (a) VALUES "
"(toDateTime('2017-07-14 05:40:00', 'Asia/Kamchatka'))",
use_client_time_zone=1
)
query = 'SELECT toInt32(a) FROM test'
inserted = self.emit_cli(query, use_client_time_zone=1)
# 1499967600 = 1500000000 - 12 * 3600
self.assertEqual(inserted, '1499967600\n1499967600\n')
query = 'SELECT * FROM test'
inserted = self.emit_cli(query, use_client_time_zone=1)
# 2017-07-14 00:40:00 = 2017-07-14 05:40:00 - 05:00:00
# (Kamchatka - Novosibirsk)
self.assertEqual(
inserted,
'2017-07-14 00:40:00\n2017-07-14 00:40:00\n'
)
inserted = self.client.execute(query, columnar=True,
settings=settings)
dt = datetime(2017, 7, 14, 0, 40)
self.assertArraysEqual(
inserted[0], self.make_numpy_d64ns([dt.isoformat()] * 2)
)
def test_use_client_timezone(self):
# Insert datetime with timezone UTC
# into column with no timezone
# using client's timezone Asia/Novosibirsk
settings = {'use_client_time_zone': True}
with patch_env_tz('Asia/Novosibirsk'):
with self.create_table(self.table_columns()):
self.client.execute(
'INSERT INTO test (a) VALUES', [self.dt_arr],
settings=settings, columnar=True
)
self.emit_cli(
"INSERT INTO test (a) VALUES ('2017-07-14 05:40:00')",
use_client_time_zone=1
)
query = 'SELECT toInt32(a) FROM test'
inserted = self.emit_cli(query, use_client_time_zone=1)
# 1499985600 = 1500000000 - 4 * 3600
self.assertEqual(inserted, '1499985600\n1499985600\n')
query = 'SELECT * FROM test'
inserted = self.emit_cli(query, use_client_time_zone=1)
self.assertEqual(
inserted,
'2017-07-14 05:40:00\n2017-07-14 05:40:00\n'
)
inserted = self.client.execute(query, columnar=True,
settings=settings)
self.assertArraysEqual(
inserted[0], self.make_numpy_d64ns([self.dt_str] * 2)
)