To_iso8601.py 6.3 KB
Newer Older
wafwerar's avatar
wafwerar 已提交
1
import time
J
update  
jiacy-jcy 已提交
2
from time import sleep
J
update  
jiacy-jcy 已提交
3

J
update  
jiacy-jcy 已提交
4 5 6
from util.log import *
from util.sql import *
from util.cases import *
J
jiacy-jcy 已提交
7
import os
J
update  
jiacy-jcy 已提交
8

J
update  
jiacy-jcy 已提交
9 10 11 12 13 14 15


class TDTestCase:

    def init(self, conn, logSql):
        tdLog.debug(f"start to excute {__file__}")
        tdSql.init(conn.cursor())
J
jiacy-jcy 已提交
16 17 18
        self.rowNum = 10
        self.ts = 1640966400000  # 2022-1-1 00:00:00.000
    def check_customize_param_ms(self):
G
Ganlin Zhao 已提交
19

wafwerar's avatar
wafwerar 已提交
20
        time_zone = time.strftime('%z')
J
jiacy-jcy 已提交
21 22
        tdSql.execute('create database db1 precision "ms"')
        tdSql.execute('use db1')
J
jiacy-jcy 已提交
23
        tdSql.execute('create table if not exists ntb(ts timestamp, c1 int, c2 timestamp)')
J
jiacy-jcy 已提交
24
        for i in range(self.rowNum):
G
Ganlin Zhao 已提交
25
            tdSql.execute("insert into ntb values(%d, %d, %d)"
J
jiacy-jcy 已提交
26 27 28 29
                        % (self.ts + i, i + 1, self.ts + i))
        tdSql.query('select to_iso8601(ts) from ntb')
        for i in range(self.rowNum):
            tdSql.checkEqual(tdSql.queryResult[i][0],f'2022-01-01T00:00:00.00{i}{time_zone}')
G
Ganlin Zhao 已提交
30

J
jiacy-jcy 已提交
31 32 33 34 35
        timezone_list = ['+0000','+0100','+0200','+0300','+0330','+0400','+0500','+0530','+0600','+0700','+0800','+0900','+1000','+1100','+1200',\
                        '+00','+01','+02','+03','+04','+05','+06','+07','+08','+09','+10','+11','+12',\
                            '+00:00','+01:00','+02:00','+03:00','+03:30','+04:00','+05:00','+05:30','+06:00','+07:00','+08:00','+09:00','+10:00','+11:00','+12:00',\
                            '-0000','-0100','-0200','-0300','-0400','-0500','-0600','-0700','-0800','-0900','-1000','-1100','-1200',\
                        '-00','-01','-02','-03','-04','-05','-06','-07','-08','-09','-10','-11','-12',\
J
update  
jiacy-jcy 已提交
36
                            '-00:00','-01:00','-02:00','-03:00','-04:00','-05:00','-06:00','-07:00','-08:00','-09:00','-10:00','-11:00','-12:00',\
J
jiacy-jcy 已提交
37
                                'z','Z']
J
jiacy-jcy 已提交
38 39 40
        for j in timezone_list:
            tdSql.query(f'select to_iso8601(ts,"{j}") from ntb')
            for i in range(self.rowNum):
J
jiacy-jcy 已提交
41
                tdSql.checkEqual(tdSql.queryResult[i][0],f'2022-01-01T00:00:00.00{i}{j}')
G
Ganlin Zhao 已提交
42

J
jiacy-jcy 已提交
43
        error_param_list = [0,100.5,'a','!']
J
jiacy-jcy 已提交
44
        for i in error_param_list:
J
jiacy-jcy 已提交
45
            tdSql.error(f'select to_iso8601(ts,"{i}") from ntb')
J
jiacy-jcy 已提交
46
        #! bug TD-16372:对于错误的时区,缺少校验
J
update  
jiacy-jcy 已提交
47
        error_timezone_param = ['+13','-13','+1300','-1300','+0001','-0001','-0330','-0530']
J
jiacy-jcy 已提交
48 49
        for i in error_timezone_param:
            tdSql.error(f'select to_iso8601(ts,"{i}") from ntb')
G
Ganlin Zhao 已提交
50

J
jiacy-jcy 已提交
51 52 53 54 55 56
    def check_base_function(self):
        tdSql.prepare()
        tdLog.printNoPrefix("==========step1:create tables==========")
        tdSql.execute('create table if not exists ntb(ts timestamp, c1 int, c2 float,c3 double,c4 timestamp)')
        tdSql.execute('create table if not exists stb(ts timestamp, c1 int, c2 float,c3 double,c4 timestamp) tags(t0 int)')
        tdSql.execute('create table if not exists stb_1 using stb tags(100)')
J
update  
jiacy-jcy 已提交
57 58 59 60 61 62 63

        tdLog.printNoPrefix("==========step2:insert data==========")
        tdSql.execute('insert into ntb values(now,1,1.55,100.555555,today())("2020-1-1 00:00:00",10,11.11,99.999999,now())(today(),3,3.333,333.333333,now())')
        tdSql.execute('insert into stb_1 values(now,1,1.55,100.555555,today())("2020-1-1 00:00:00",10,11.11,99.999999,now())(today(),3,3.333,333.333333,now())')

        tdSql.query("select to_iso8601(ts) from ntb")
        tdSql.checkRows(3)
J
update  
jiacy-jcy 已提交
64
        tdSql.query("select c1 from ntb where ts = to_iso8601(1577808000000)")
J
update  
jiacy-jcy 已提交
65 66
        tdSql.checkRows(1)
        tdSql.checkData(0,0,10)
J
update  
jiacy-jcy 已提交
67
        tdSql.query("select * from ntb where ts = to_iso8601(1577808000000)")
J
update  
jiacy-jcy 已提交
68 69 70
        tdSql.checkRows(1)
        tdSql.query("select to_iso8601(ts) from ntb where ts=today()")
        tdSql.checkRows(1)
J
jiacy-jcy 已提交
71
        for i in range(0,3):
J
update  
jiacy-jcy 已提交
72
            tdSql.query("select to_iso8601(1) from ntb")
J
jiacy-jcy 已提交
73
            tdSql.checkData(i,0,"1970-01-01T08:00:01+0800")
J
update  
jiacy-jcy 已提交
74
            tdSql.checkRows(3)
J
update  
jiacy-jcy 已提交
75 76
        tdSql.query("select to_iso8601(ts) from ntb")
        tdSql.checkRows(3)
J
update  
jiacy-jcy 已提交
77
        tdSql.query("select to_iso8601(ts) from db.ntb")
G
Ganlin Zhao 已提交
78

J
update  
jiacy-jcy 已提交
79 80 81 82
        tdSql.query("select to_iso8601(today()) from ntb")
        tdSql.checkRows(3)
        tdSql.query("select to_iso8601(now()) from ntb")
        tdSql.checkRows(3)
G
Ganlin Zhao 已提交
83

J
update  
jiacy-jcy 已提交
84 85 86
        tdSql.error("select to_iso8601(timezone()) from ntb")
        tdSql.error("select to_iso8601('abc') from ntb")

J
jiacy-jcy 已提交
87 88 89 90 91 92 93
        for i in ['+','-','*','/']:
            tdSql.query(f"select to_iso8601(today()) {i}null from ntb")
            tdSql.checkRows(3)
            tdSql.checkData(0,0,None)
            tdSql.query(f"select to_iso8601(today()) {i}null from db.ntb")
            tdSql.checkRows(3)
            tdSql.checkData(0,0,None)
J
jiacy-jcy 已提交
94 95
        tdSql.query("select to_iso8601(9223372036854775807) from ntb")
        tdSql.checkRows(3)
J
jiacy-jcy 已提交
96
        # bug TD-15207
J
jiacy-jcy 已提交
97 98 99 100 101 102
        # tdSql.query("select to_iso8601(10000000000) from ntb")
        # tdSql.checkData(0,0,None)
        # tdSql.query("select to_iso8601(-1) from ntb")
        # tdSql.checkRows(3)
        # tdSql.query("select to_iso8601(-10000000000) from ntb")
        # tdSql.checkData(0,0,None)
J
jiacy-jcy 已提交
103 104 105 106
        err_param = [1.5,'a','c2']
        for i in err_param:
            tdSql.error(f"select to_iso8601({i}) from ntb")
            tdSql.error(f"select to_iso8601({i}) from db.ntb")
G
Ganlin Zhao 已提交
107

J
update  
jiacy-jcy 已提交
108
        tdSql.query("select to_iso8601(now) from stb")
J
jiacy-jcy 已提交
109
        tdSql.checkRows(3)
J
update  
jiacy-jcy 已提交
110 111
        tdSql.query("select to_iso8601(now()) from stb")
        tdSql.checkRows(3)
J
jiacy-jcy 已提交
112
        tdSql.query("select to_iso8601(1) from stb")
J
jiacy-jcy 已提交
113
        for i in range(0,3):
J
jiacy-jcy 已提交
114
            tdSql.checkData(i,0,"1970-01-01T08:00:01+0800")
J
update  
jiacy-jcy 已提交
115 116 117 118 119 120 121
            tdSql.checkRows(3)
        tdSql.query("select to_iso8601(ts) from stb")
        tdSql.checkRows(3)
        tdSql.query("select to_iso8601(ts)+1 from stb")
        tdSql.checkRows(3)
        tdSql.query("select to_iso8601(ts)+'a' from stb ")
        tdSql.checkRows(3)
J
jiacy-jcy 已提交
122
        for i in ['+','-','*','/']:
J
jiacy-jcy 已提交
123
            tdSql.query(f"select to_iso8601(today()) {i}null from stb")
J
jiacy-jcy 已提交
124 125
            tdSql.checkRows(3)
            tdSql.checkData(0,0,None)
J
jiacy-jcy 已提交
126
            tdSql.query(f"select to_iso8601(today()) {i}null from db.stb")
J
jiacy-jcy 已提交
127 128
            tdSql.checkRows(3)
            tdSql.checkData(0,0,None)
G
Ganlin Zhao 已提交
129

J
jiacy-jcy 已提交
130 131 132
    def run(self):  # sourcery skip: extract-duplicate-method
        self.check_base_function()
        self.check_customize_param_ms()
J
jiacy-jcy 已提交
133

J
update  
jiacy-jcy 已提交
134 135 136 137 138 139
    def stop(self):
        tdSql.close()
        tdLog.success(f"{__file__} successfully executed")

tdCases.addLinux(__file__, TDTestCase())
tdCases.addWindows(__file__, TDTestCase())