text: from datetime import datetime, date
  from unittest.mock import patch
  
  try:
      import numpy as np
  except ImportError:
      np = None
  
  try:
      import pandas as pd
  except ImportError:
      pd = None
  
  from pytz import timezone, utc, UnknownTimeZoneError
  import tzlocal
  
  from tests.numpy.testcase import NumpyBaseTestCase
  from tests.util import require_server_version, bust_tzlocal_cache, patch_env_tz
  
  
  class BaseDateTimeTestCase(NumpyBaseTestCase):
      def setUp(self):
          super(BaseDateTimeTestCase, self).setUp()
          bust_tzlocal_cache()
  
      def make_numpy_d64ns(self, items):
          return np.array(items, dtype='datetime64[ns]')
  
  
  class DateTimeTestCase(BaseDateTimeTestCase):
      def test_datetime_type(self):
          query = 'SELECT now()'
  
          rv = self.client.execute(query, columnar=True)
          self.assertIsInstance(rv[0][0], np.datetime64)
  
      @require_server_version(20, 1, 2)
      def test_datetime64_type(self):
          query = 'SELECT now64()'
  
          rv = self.client.execute(query, columnar=True)
          self.assertIsInstance(rv[0][0], np.datetime64)
  
      def test_simple(self):
          with self.create_table('a Date, b DateTime'):
              data = [
                  np.array(['2012-10-25'], dtype='datetime64[D]'),
                  np.array(['2012-10-25T14:07:19'], dtype='datetime64[ns]')
              ]
              self.client.execute(
                  'INSERT INTO test (a, b) VALUES', data, columnar=True
              )
  
              query = 'SELECT * FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(inserted, '2012-10-25\t2012-10-25 14:07:19\n')
  
              inserted = self.client.execute(query, columnar=True)
              self.assertArraysEqual(
                  inserted[0], np.array(['2012-10-25'], dtype='datetime64[D]')
              )
              self.assertArraysEqual(
                  inserted[1], self.make_numpy_d64ns(['2012-10-25T14:07:19'])
              )
  
      def test_nullable_date(self):
          with self.create_table('a Nullable(Date)'):
              data = [
                  np.array([None, date(2012, 10, 25), None, date(2017, 6, 23)],
                           dtype=object)
              ]
              self.client.execute(
                  'INSERT INTO test (a) VALUES', data, columnar=True
              )
  
              query = 'SELECT * FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(
                  inserted, '\\N\n2012-10-25\n\\N\n2017-06-23\n'
              )
  
              inserted = self.client.execute(query, columnar=True)
              self.assertArraysEqual(inserted[0], data[0])
              self.assertEqual(inserted[0].dtype, object)
  
      def test_nullable_datetime(self):
          with self.create_table('a Nullable(DateTime)'):
              data = [
                  np.array([
                      None, datetime(2012, 10, 25, 14, 7, 19),
                      None, datetime(2017, 6, 23, 19, 10, 15)
                  ], dtype=object)
              ]
              self.client.execute(
                  'INSERT INTO test (a) VALUES', data, columnar=True
              )
  
              query = 'SELECT * FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(
                  inserted,
                  '\\N\n2012-10-25 14:07:19\n\\N\n2017-06-23 19:10:15\n'
              )
  
              inserted = self.client.execute(query, columnar=True)
  
              self.assertArraysEqual(inserted[0], data[0])
              self.assertEqual(inserted[0].dtype, object)
  
      def test_handle_errors_from_tzlocal(self):
          with patch('tzlocal.get_localzone') as mocked:
              mocked.side_effect = UnknownTimeZoneError()
              self.client.execute('SELECT now()')
  
          if hasattr(tzlocal, 'get_localzone_name'):
              with patch('tzlocal.get_localzone_name') as mocked:
                  mocked.side_effect = None
                  self.client.execute('SELECT now()')
  
      @require_server_version(20, 1, 2)
      def test_datetime64_frac_trunc(self):
          with self.create_table('a DateTime64'):
              data = [self.make_numpy_d64ns(['2012-10-25T14:07:19.125600'])]
              self.client.execute(
                  'INSERT INTO test (a) VALUES', data, columnar=True
              )
  
              query = 'SELECT * FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(inserted, '2012-10-25 14:07:19.125\n')
  
              inserted = self.client.execute(query, columnar=True)
              self.assertArraysEqual(
                  inserted[0], self.make_numpy_d64ns(['2012-10-25T14:07:19.125'])
              )
  
      @require_server_version(20, 1, 2)
      def test_datetime64_explicit_frac(self):
          with self.create_table('a DateTime64(1)'):
              data = [self.make_numpy_d64ns(['2012-10-25T14:07:19.125600'])]
              self.client.execute(
                  'INSERT INTO test (a) VALUES', data, columnar=True
              )
  
              query = 'SELECT * FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(inserted, '2012-10-25 14:07:19.1\n')
  
              inserted = self.client.execute(query, columnar=True)
              self.assertArraysEqual(
                  inserted[0], self.make_numpy_d64ns(['2012-10-25T14:07:19.1'])
              )
  
      def test_insert_integers_datetime(self):
          with self.create_table('a DateTime'):
              self.client.execute(
                  'INSERT INTO test (a) VALUES',
                  [np.array([1530211034], dtype=np.uint32)], columnar=True
              )
  
              query = 'SELECT toUInt32(a), a FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(inserted, '1530211034\t2018-06-28 21:37:14\n')
  
      @require_server_version(20, 1, 2)
      def test_insert_integers_datetime64(self):
          with self.create_table('a DateTime64'):
              self.client.execute(
                  'INSERT INTO test (a) VALUES',
                  [np.array([1530211034123], dtype=np.uint64)], columnar=True
              )
  
              query = 'SELECT toUInt32(a), a FROM test'
              inserted = self.emit_cli(query)
              self.assertEqual(inserted, '1530211034\t2018-06-28 21:37:14.123\n')
  
      def test_insert_integer_bounds(self):
          with self.create_table('a DateTime'):
              self.client.execute(
                  'INSERT INTO test (a) VALUES',
                  [np.array([0, 1, 1500000000, 2**32-1], dtype=np.uint32)],
                  columnar=True
              )
  
              query = 'SELECT toUInt32(a) FROM test ORDER BY a'
              inserted = self.emit_cli(query)
              self.assertEqual(inserted, '0\n1\n1500000000\n4294967295\n')
  
  
  class DateTimeTimezonesTestCase(BaseDateTimeTestCase):
      dt_type = 'DateTime'
  
      def make_tz_numpy_array(self, dt, tz_name):
          dtype = 'datetime64[ns]'
  
          return pd.to_datetime(np.array([dt] * 2, dtype=dtype)) \
              .tz_localize(tz_name).to_numpy(dtype)
  
      # Asia/Kamchatka = UTC+12
      # Asia/Novosibirsk = UTC+7
      # Europe/Moscow = UTC+3
  
      # 1500010800 second since epoch in Europe/Moscow.
      # 1500000000 second since epoch in UTC.
      dt = datetime(2017, 7, 14, 5, 40)
      dt_str = dt.isoformat()
  
      # properties for lazy evaluation for dealing with AttributeError when no
      # numpy/pandas installed
      @property
      def dt_arr(self):
          return np.array([self.dt_str], dtype='datetime64[s]')
  
      @property
      def dt_tz(self):
          return pd.to_datetime(self.dt_arr).tz_localize('Asia/Kamchatka')
  
      col_tz_name = 'Asia/Novosibirsk'
      col_tz = timezone(col_tz_name)
  
      # INSERTs and SELECTs must be the same as clickhouse-client's
      # if column has no timezone.
  
      def table_columns(self, with_tz=False):
          if not with_tz:
              return 'a {}'.format(self.dt_type)
  
          return "a {}('{}')".format(self.dt_type, self.col_tz_name)
  
      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_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)
                  )
  
      @require_server_version(1, 1, 54337)
      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)
                  )
  
      @require_server_version(1, 1, 54337)
      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)
                  )
  
      @require_server_version(1, 1, 54337)
      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)
                  )
  
      @require_server_version(1, 1, 54337)
      def test_column_use_client_timezone(self):
          # Insert datetime with no timezone
          # into column with timezone Asia/Novosibirsk
          # using client's timezone Europe/Moscow
  
          settings = {'use_client_time_zone': True}
  
          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],
                      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_tz_numpy_array(self.dt, self.col_tz_name)
                  )
  
      @require_server_version(1, 1, 54337)
      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)
                  )
  
      @require_server_version(1, 1, 54337)
      def test_datetime_with_timezone_column_use_client_timezone(self):
          # Insert datetime with timezone Asia/Kamchatka
          # into column with timezone Asia/Novosibirsk
          # using client's timezone (Europe/Moscow)
  
          settings = {'use_client_time_zone': True}
  
          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],
                      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_tz_numpy_array(dt, self.col_tz_name)
                  )
  
  
  class DateTime64TimezonesTestCase(DateTimeTimezonesTestCase):
      dt_type = 'DateTime64'
      required_server_version = (20, 1, 2)
  
      def table_columns(self, with_tz=False):
          if not with_tz:
              return 'a {}(0)'.format(self.dt_type)
  
          return "a {}(0, '{}')".format(self.dt_type, self.col_tz_name)
  