pymssql/tests/test_datetime.py

# -*- coding: utf-8 -*-

import datetime
import unittest

from pymssql import datetime2
from .helpers import TestCaseWithTable, mssqlconn, get_sql_server_version

import pytest


class TestCaseDATETIME2(unittest.TestCase, TestCaseWithTable):

    @classmethod
    def setup_class(cls):
        cls.conn = mssqlconn()
        if get_sql_server_version(cls.conn) < 2008:
            pytest.skip("DATETIME2 field type isn't supported by SQL Server versions prior to 2008.")
        if cls.conn.tds_version < 7.3:
            pytest.skip("DATETIME2 field type isn't supported by TDS protocol older than 7.3.")
        cls.create_table()


class Test_DATETIME2(TestCaseDATETIME2):

    table_name = "test_datetime2"
    ddl_create = f"CREATE TABLE {table_name} (test DATETIME2)"
    min_date = datetime2(1, 1, 1, 0, 0, 0, 0)
    max_date = datetime2(9999, 12, 31, 23, 59, 59, 999999)

    def test_min_select(self):
        self.conn.execute_query("SELECT CAST ('0001-1-1 0:0:0' as DATETIME2)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res == self.min_date

    def test_min_insert(self):
        res = self.insert_and_select('test', self.min_date)
        assert isinstance(res, datetime.datetime)
        assert res == self.min_date

    def test_max_select(self):
        self.conn.execute_query("SELECT CAST ('9999-12-31 23:59:59.9999999' as DATETIME2)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res == self.max_date

    def test_max_insert(self):
        res = self.insert_and_select('test', self.max_date)
        assert isinstance(res, datetime.datetime)
        assert res == self.max_date

    def test_datetime2(self):
        testval = datetime2(2013, 1, 2, 3, 4, 5, 6)
        res = self.insert_and_select('test', testval)
        assert isinstance(res, datetime.datetime)
        assert testval == res

    def test_truncate(self):
        self.conn.execute_query("SELECT CAST ('2024-1-1 0:0:0.1234567' as DATETIME2)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res == datetime.datetime(2024, 1, 1, 0, 0, 0, 123456)


class Test_695(TestCaseDATETIME2):
    """
    See issue #695
    """
    table_name = "dbo.test695"
    ddl_create = f"""
            CREATE TABLE {table_name} (
                valid_from DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
                valid_to DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
                PERIOD FOR SYSTEM_TIME (valid_from,valid_to),
                id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
                test VARCHAR(255) NOT NULL,
                )
        """
        #WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.test_history));

    def test_695(self):
        self.conn.execute_non_query(f"INSERT INTO {self.table_name} (test) VALUES (%s)", ("TEST", ))
        self.conn.execute_query(f"SELECT valid_from, valid_to FROM {self.table_name}")
        rows = tuple(self.conn)
        res = rows[0][1]
        assert isinstance(res, datetime.datetime)
        #print(rows)


class Test_DATETIME(TestCaseDATETIME2):

    table_name = "test_datetime"
    ddl_create = f"CREATE TABLE {table_name} (test DATETIME)"
    min_date = datetime.datetime(1753, 1, 1, 0, 0, 0, 0)
    max_date = datetime.datetime(9999, 12, 31, 23, 59, 59, 997000)

    def test_min_select(self):
        self.conn.execute_query("SELECT CAST ('1753-1-1 0:0:0' as DATETIME)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert not isinstance(res, datetime2)
        assert res == self.min_date

    def test_min_insert(self):
        res = self.insert_and_select('test', self.min_date)
        assert isinstance(res, datetime.datetime)
        assert not isinstance(res, datetime2)
        assert res == self.min_date

    def test_max_select(self):
        self.conn.execute_query("SELECT CAST ('9999-12-31 23:59:59.997' as DATETIME)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert not isinstance(res, datetime2)
        assert res == self.max_date

    def test_max_insert(self):
        res = self.insert_and_select('test', self.max_date)
        assert isinstance(res, datetime.datetime)
        assert not isinstance(res, datetime2)
        assert res == self.max_date

    def test_datetime(self):
        """
        Test for issue at https://code.google.com/p/pymssql/issues/detail?id=118
        datetime values are rounded to increments of .000, .003, or .007 seconds,
        see https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql
        """
        for mks in (0, 3000, 7000):
            testval = datetime.datetime(2013, 1, 2, 3, 4, 5, mks)
            self.conn.execute_non_query(f"DELETE FROM {self.table_name}")
            res = self.insert_and_select('test', testval)
            assert isinstance(res, datetime.datetime)
            assert not isinstance(res, datetime2)
            assert res == testval

    def test_datetime_params_as_dict(self):
        testval = datetime.datetime(2013, 1, 2, 3, 4, 5, 3000)
        res = self.insert_and_select('test', testval, params_as_dict=True)
        assert isinstance(res, datetime.datetime)
        assert not isinstance(res, datetime2)
        assert res == testval


class Test_DATE(TestCaseDATETIME2):

    table_name = "test_date"
    ddl_create = f"CREATE TABLE {table_name} (test DATE)"
    min_date = datetime.date(1, 1, 1)
    max_date = datetime.date(9999, 12, 31)

    def test_min_select(self):
        self.conn.execute_query("SELECT CAST ('0001-1-1' as DATE)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.date)
        assert res == self.min_date

    def test_min_insert(self):
        res = self.insert_and_select('test', self.min_date)
        assert isinstance(res, datetime.date)
        assert res == self.min_date

    def test_max_select(self):
        self.conn.execute_query("SELECT CAST ('9999-12-31' as DATE)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.date)
        assert res == self.max_date

    def test_max_insert(self):
        res = self.insert_and_select('test', self.max_date)
        assert isinstance(res, datetime.date)
        assert res == self.max_date

    def test_date(self):
        testval = datetime.date(2013, 1, 2)
        res = self.insert_and_select('test', testval)
        assert isinstance(res, datetime.date)
        assert res == testval

    def test_ancient_date(self):
        testval = datetime.date(13, 1, 2)
        res = self.insert_and_select('test', testval)
        assert isinstance(res, datetime.date)
        assert res == testval


class Test_TIME(TestCaseDATETIME2):

    table_name = "test_time"
    ddl_create = f"CREATE TABLE {table_name} (test TIME)"
    min_time = datetime.time(0, 0, 0, 0)
    max_time = datetime.time(23, 59, 59, 999999)

    def test_min_select(self):
        self.conn.execute_query("SELECT CAST ('0:0:0.0' as TIME)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.time)
        assert res == self.min_time

    def test_min_insert(self):
        res = self.insert_and_select('test', self.min_time)
        assert isinstance(res, datetime.time)
        assert res == self.min_time

    def test_max_select(self):
        self.conn.execute_query("SELECT CAST ('23:59:59.9999999' as TIME)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.time)
        assert res == self.max_time

    def test_max_insert(self):
        res = self.insert_and_select('test', self.max_time)
        assert isinstance(res, datetime.time)
        assert res == self.max_time

    def test_time(self):
        testval = datetime.time(3, 4, 5, 3000)
        res = self.insert_and_select('test', testval)
        assert isinstance(res, datetime.time)
        assert res == testval


class Test_DATETIMEOFFSET(TestCaseDATETIME2):

    table_name = "test_time"
    ddl_create = f"""CREATE TABLE {table_name} (
                        id INT default 1,
                        DateCreated DATETIMEOFFSET default sysdatetimeoffset()
                    )"""

    def test_649(self):
        """
        Test #649
        """
        self.conn.execute_non_query(
            f"INSERT INTO {self.table_name} (id) VALUES (%s)", (2, ))
        self.conn.execute_query(
            f'select DateCreated from {self.table_name} where id = 2')
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res.strftime('%z') != ''

    def test_select_cast_0(self):
        self.conn.execute_query(
            "SELECT CAST ('2019-06-20 09:54:40.09550' as DATETIMEOFFSET)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res == datetime.datetime(2019, 6, 20, 9, 54, 40, 95500,
                                        tzinfo=datetime.timezone.utc)

    def test_select_cast(self):
        self.conn.execute_query(
            "SELECT CAST ('2019-06-20 09:54:40.09550 +04:00' as DATETIMEOFFSET)")
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res == datetime.datetime(2019, 6, 20, 9, 54, 40, 95500,
                    tzinfo=datetime.timezone(datetime.timedelta(seconds=4*60*60)))

    def test_insert_select(self):
        testval = datetime.datetime(3, 4, 5, 3,
                    tzinfo=datetime.timezone(datetime.timedelta(seconds=4*60*60)))
        self.conn.execute_non_query(
            f"INSERT INTO {self.table_name} (id, DateCreated) VALUES (%s, %s)",
            (22, testval))
        self.conn.execute_query(
            f'select DateCreated from {self.table_name} where id = 22')
        res = tuple(self.conn)[0][0]
        assert isinstance(res, datetime.datetime)
        assert res.strftime('%z') != ''
        assert res == testval