Skip to content

T
TDengine

taosdata / TDengine
1 年多 前同步成功

通知 1185
Star 22016
Fork 4786
T
TDengine
切换分支/标签
查找文件 普通视图历史永久链接Permalink
timetruncate.py 18.2 KB
Newer Older
J
add case for max and min  
jiacy-jcy 已提交
1 2 3 4 5 
import taos
from util.log import *
from util.cases import *
from util.sql import *
import numpy as np
J
update  
jiacy-jcy 已提交
6 7 
import time
from datetime import datetime
J
add case for max and min  
jiacy-jcy 已提交
8 9 10 11 12 13 14 15 

class TDTestCase:
    def init(self, conn, logSql):
        tdLog.debug("start to execute %s" % __file__)
        tdSql.init(conn.cursor())

        self.rowNum = 10
        self.ts = 1537146000000  # 2018-9-17 09:00:00.000
G
fix test cases  
Ganlin Zhao 已提交
16
J
update  
jiacy-jcy 已提交
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 
        self.ts_str = [
            '2020-1-1',
            '2020-2-1 00:00:01',
            '2020-3-1 00:00:00.001',
            '2020-4-1 00:00:00.001002',
            '2020-5-1 00:00:00.001002001'

        ]
        self.db_param_precision = ['ms','us','ns']
        self.time_unit = ['1w','1d','1h','1m','1s','1a','1u']
    def get_ms_timestamp(self,ts_str):
        _ts_str = ts_str
        if " " in ts_str:
            p = ts_str.split(" ")[1]
            if len(p) > 15 :
                _ts_str = ts_str[:-3]
        if ':' in _ts_str and '.' in _ts_str:
            timestamp = datetime.strptime(_ts_str, "%Y-%m-%d %H:%M:%S.%f")
            date_time = int(int(time.mktime(timestamp.timetuple()))*1000 + timestamp.microsecond/1000)
        elif ':' in _ts_str and '.' not in _ts_str:
            timestamp = datetime.strptime(_ts_str, "%Y-%m-%d %H:%M:%S")
            date_time = int(int(time.mktime(timestamp.timetuple()))*1000 + timestamp.microsecond/1000)
        else:
            timestamp = datetime.strptime(_ts_str, "%Y-%m-%d")
            date_time = int(int(time.mktime(timestamp.timetuple()))*1000 + timestamp.microsecond/1000)
        return date_time
    def get_us_timestamp(self,ts_str):
        _ts = self.get_ms_timestamp(ts_str) * 1000
        if " " in ts_str:
            p = ts_str.split(" ")[1]
            if len(p) > 12:
                us_ts = p[12:15]
                _ts += int(us_ts)
        return _ts
    def get_ns_timestamp(self,ts_str):
        _ts = self.get_us_timestamp(ts_str) *1000
        if " " in ts_str:
            p = ts_str.split(" ")[1]
            if len(p) > 15:
                us_ts = p[15:]
                _ts += int(us_ts)
        return _ts
    def time_transform(self,ts_str,precision):
        date_time = []
        if precision == 'ms':
            for i in ts_str:
                date_time.append(self.get_ms_timestamp(i))
        elif precision == 'us':
            for i in ts_str:
                date_time.append(self.get_us_timestamp(i))
        elif precision == 'ns':
            for i in ts_str:
                date_time.append(self.get_us_timestamp(i))
        return date_time
    def function_check_ntb(self):
        
        for precision in self.db_param_precision:
            tdSql.execute('drop database if exists db')
            tdSql.execute(f'create database db precision "{precision}"')
            tdSql.execute('use db')
            tdSql.execute('create table ntb (ts timestamp,c0 int)')
            for ts in self.ts_str:
                tdSql.execute(f'insert into ntb values("{ts}",1)')
            date_time = self.time_transform(self.ts_str,precision)
            
            for unit in self.time_unit:
                if (unit.lower() == '1u' and precision.lower() == 'ms') or () :
                    tdSql.error(f'select timetruncate(ts,{unit}) from ntb')
                elif precision.lower() == 'ms':
                    tdSql.query(f'select timetruncate(ts,{unit}) from ntb')
                    if unit.lower() == '1a':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_ms_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]))
                    elif unit.lower() == '1s':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_ms_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000)*1000)
                    elif unit.lower() == '1m':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_ms_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/60)*60*1000)
                    elif unit.lower() == '1h':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_ms_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/60/60)*60*60*1000  )
                    elif unit.lower() == '1d':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_ms_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/60/60/24)*24*60*60*1000)  
                    elif unit.lower() == '1w':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_ms_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/60/60/24/7)*7*24*60*60*1000)         
                elif precision.lower() == 'us':
                    tdSql.query(f'select timetruncate(ts,{unit}) from ntb')
                    if unit.lower() == '1u':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]))
                    elif unit.lower() == '1a':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000)*1000)
                    elif unit.lower() == '1s':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/1000)*1000*1000)
                    elif unit.lower() == '1m':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/1000/60)*60*1000*1000)
                    elif unit.lower() == '1h':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/1000/60/60)*60*60*1000*1000  )
                    elif unit.lower() == '1d':
                        for i in range(len(self.ts_str)):
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/1000/60/60/24)*24*60*60*1000*1000 ) 
                    elif unit.lower() == '1w':
                        for i in range(len(self.ts_str)):
                            print(tdSql.queryResult[i][0])
                            ts_result = self.get_us_timestamp(str(tdSql.queryResult[i][0]))
                            tdSql.checkEqual(ts_result,int(date_time[i]/1000/1000/60/60/24/7)*7*24*60*60*1000*1000)
J
add case for max and min  
jiacy-jcy 已提交
145 
    def run(self):
J
update  
jiacy-jcy 已提交
146 147 
        self.function_check_ntb()
        # tdSql.prepare()
J
add case for max and min  
jiacy-jcy 已提交
148
J
update  
jiacy-jcy 已提交
149 150 
        # intData = []
        # floatData = []
J
add case for max and min  
jiacy-jcy 已提交
151
J
update  
jiacy-jcy 已提交
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 
        # tdSql.execute('''create table stb(ts timestamp, col1 tinyint, col2 smallint, col3 int, col4 bigint, col5 float, col6 double, 
        #             col7 bool, col8 binary(20), col9 nchar(20), col11 tinyint unsigned, col12 smallint unsigned, col13 int unsigned, col14 bigint unsigned) tags(loc nchar(20))''')
        # tdSql.execute("create table stb_1 using stb tags('beijing')")
        # tdSql.execute('''create table ntb(ts timestamp, col1 tinyint, col2 smallint, col3 int, col4 bigint, col5 float, col6 double, 
        #             col7 bool, col8 binary(20), col9 nchar(20), col11 tinyint unsigned, col12 smallint unsigned, col13 int unsigned, col14 bigint unsigned)''')
        # for i in range(self.rowNum):
        #     tdSql.execute("insert into ntb values(%d, %d, %d, %d, %d, %f, %f, %d, 'taosdata%d', '涛思数据%d', %d, %d, %d, %d)" 
        #                 % (self.ts + i, i + 1, i + 1, i + 1, i + 1, i + 0.1, i + 0.1, i % 2, i + 1, i + 1, i + 1, i + 1, i + 1, i + 1))
        #     intData.append(i + 1)
        #     floatData.append(i + 0.1)
        # for i in range(self.rowNum):
        #     tdSql.execute("insert into stb_1 values(%d, %d, %d, %d, %d, %f, %f, %d, 'taosdata%d', '涛思数据%d', %d, %d, %d, %d)" 
        #                 % (self.ts + i, i + 1, i + 1, i + 1, i + 1, i + 0.1, i + 0.1, i % 2, i + 1, i + 1, i + 1, i + 1, i + 1, i + 1))
        #     intData.append(i + 1)
        #     floatData.append(i + 0.1)
J
add case for max and min  
jiacy-jcy 已提交
167
J
update  
jiacy-jcy 已提交
168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 
        # tdSql.query("select timetruncate(1,1d) from ntb")
        # tdSql.checkRows(10)
        # tdSql.error("select timetruncate(1,1u) from ntb")
        # #tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1a) from ntb")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1m) from ntb")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1h) from ntb")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(ts,1d) from ntb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 08:00:00.000")
        # tdSql.query("select timetruncate(ts,1h) from ntb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1m) from ntb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1s) from ntb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1a) from ntb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.checkData(1,0,"2018-09-17 09:00:00.001")
        # tdSql.checkData(2,0,"2018-09-17 09:00:00.002")
        # tdSql.checkData(3,0,"2018-09-17 09:00:00.003")
        # tdSql.checkData(4,0,"2018-09-17 09:00:00.004")
        # tdSql.checkData(5,0,"2018-09-17 09:00:00.005")
        # tdSql.checkData(6,0,"2018-09-17 09:00:00.006")
        # tdSql.checkData(7,0,"2018-09-17 09:00:00.007")
        # tdSql.checkData(8,0,"2018-09-17 09:00:00.008")
        # tdSql.checkData(9,0,"2018-09-17 09:00:00.009")
        # # tdSql.query("select timetruncate(ts,1u) from ntb")
        # # tdSql.checkRows(10)
        # # tdSql.checkData(0,0,"2018-09-17 09:00:00.000000")
        # # tdSql.checkData(1,0,"2018-09-17 09:00:00.001000")
        # # tdSql.checkData(2,0,"2018-09-17 09:00:00.002000")
        # # tdSql.checkData(3,0,"2018-09-17 09:00:00.003000")
        # # tdSql.checkData(4,0,"2018-09-17 09:00:00.004000")
        # # tdSql.checkData(5,0,"2018-09-17 09:00:00.005000")
        # # tdSql.checkData(6,0,"2018-09-17 09:00:00.006000")
        # # tdSql.checkData(7,0,"2018-09-17 09:00:00.007000")
        # # tdSql.checkData(8,0,"2018-09-17 09:00:00.008000")
        # # tdSql.checkData(9,0,"2018-09-17 09:00:00.009000")
        # # tdSql.query("select timetruncate(ts,1b) from ntb")
        # # tdSql.checkRows(10)
        # # tdSql.checkData(0,0,"2018-09-17 09:00:00.000000000")
        # # tdSql.checkData(1,0,"2018-09-17 09:00:00.001000000")
        # # tdSql.checkData(2,0,"2018-09-17 09:00:00.002000000")
        # # tdSql.checkData(3,0,"2018-09-17 09:00:00.003000000")
        # # tdSql.checkData(4,0,"2018-09-17 09:00:00.004000000")
        # # tdSql.checkData(5,0,"2018-09-17 09:00:00.005000000")
        # # tdSql.checkData(6,0,"2018-09-17 09:00:00.006000000")
        # # tdSql.checkData(7,0,"2018-09-17 09:00:00.007000000")
        # # tdSql.checkData(8,0,"2018-09-17 09:00:00.008000000")
        # # tdSql.checkData(9,0,"2018-09-17 09:00:00.009000000")
J
add case for max and min  
jiacy-jcy 已提交
226 227
J
update  
jiacy-jcy 已提交
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 
        # tdSql.query("select timetruncate(1,1d) from stb")
        # tdSql.checkRows(10)
        # tdSql.error("select timetruncate(1,1u) from stb")
        # #tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1a) from stb")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1m) from stb")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1h) from stb")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(ts,1d) from stb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 08:00:00.000")
        # tdSql.query("select timetruncate(ts,1h) from stb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1m) from stb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1s) from stb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1a) from stb")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.checkData(1,0,"2018-09-17 09:00:00.001")
        # tdSql.checkData(2,0,"2018-09-17 09:00:00.002")
        # tdSql.checkData(3,0,"2018-09-17 09:00:00.003")
        # tdSql.checkData(4,0,"2018-09-17 09:00:00.004")
        # tdSql.checkData(5,0,"2018-09-17 09:00:00.005")
        # tdSql.checkData(6,0,"2018-09-17 09:00:00.006")
        # tdSql.checkData(7,0,"2018-09-17 09:00:00.007")
        # tdSql.checkData(8,0,"2018-09-17 09:00:00.008")
        # tdSql.checkData(9,0,"2018-09-17 09:00:00.009")
        # # tdSql.query("select timetruncate(ts,1u) from stb")
        # # tdSql.checkRows(10)
        # # tdSql.checkData(0,0,"2018-09-17 09:00:00.000000")
        # # tdSql.checkData(1,0,"2018-09-17 09:00:00.001000")
        # # tdSql.checkData(2,0,"2018-09-17 09:00:00.002000")
        # # tdSql.checkData(3,0,"2018-09-17 09:00:00.003000")
        # # tdSql.checkData(4,0,"2018-09-17 09:00:00.004000")
        # # tdSql.checkData(5,0,"2018-09-17 09:00:00.005000")
        # # tdSql.checkData(6,0,"2018-09-17 09:00:00.006000")
        # # tdSql.checkData(7,0,"2018-09-17 09:00:00.007000")
        # # tdSql.checkData(8,0,"2018-09-17 09:00:00.008000")
        # # tdSql.checkData(9,0,"2018-09-17 09:00:00.009000")
        # # tdSql.query("select timetruncate(ts,1b) from stb")
        # # tdSql.checkRows(10)
        # # tdSql.checkData(0,0,"2018-09-17 09:00:00.000000000")
        # # tdSql.checkData(1,0,"2018-09-17 09:00:00.001000000")
        # # tdSql.checkData(2,0,"2018-09-17 09:00:00.002000000")
        # # tdSql.checkData(3,0,"2018-09-17 09:00:00.003000000")
        # # tdSql.checkData(4,0,"2018-09-17 09:00:00.004000000")
        # # tdSql.checkData(5,0,"2018-09-17 09:00:00.005000000")
        # # tdSql.checkData(6,0,"2018-09-17 09:00:00.006000000")
        # # tdSql.checkData(7,0,"2018-09-17 09:00:00.007000000")
        # # tdSql.checkData(8,0,"2018-09-17 09:00:00.008000000")
        # # tdSql.checkData(9,0,"2018-09-17 09:00:00.009000000")
J
update testcase  
jiacy-jcy 已提交
286
J
update  
jiacy-jcy 已提交
287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 
        # tdSql.query("select timetruncate(1,1d) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.error("select timetruncate(1,1u) from stb_1")
        # #tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1a) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1m) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(1,1h) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.query("select timetruncate(ts,1d) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 08:00:00.000")
        # tdSql.query("select timetruncate(ts,1h) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1m) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1s) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.query("select timetruncate(ts,1a) from stb_1")
        # tdSql.checkRows(10)
        # tdSql.checkData(0,0,"2018-09-17 09:00:00.000")
        # tdSql.checkData(1,0,"2018-09-17 09:00:00.001")
        # tdSql.checkData(2,0,"2018-09-17 09:00:00.002")
        # tdSql.checkData(3,0,"2018-09-17 09:00:00.003")
        # tdSql.checkData(4,0,"2018-09-17 09:00:00.004")
        # tdSql.checkData(5,0,"2018-09-17 09:00:00.005")
        # tdSql.checkData(6,0,"2018-09-17 09:00:00.006")
        # tdSql.checkData(7,0,"2018-09-17 09:00:00.007")
        # tdSql.checkData(8,0,"2018-09-17 09:00:00.008")
        # tdSql.checkData(9,0,"2018-09-17 09:00:00.009")
        # # tdSql.query("select timetruncate(ts,1u) from stb_1")
        # # tdSql.checkRows(10)
        # # tdSql.checkData(0,0,"2018-09-17 09:00:00.000000")
        # # tdSql.checkData(1,0,"2018-09-17 09:00:00.001000")
        # # tdSql.checkData(2,0,"2018-09-17 09:00:00.002000")
        # # tdSql.checkData(3,0,"2018-09-17 09:00:00.003000")
        # # tdSql.checkData(4,0,"2018-09-17 09:00:00.004000")
        # # tdSql.checkData(5,0,"2018-09-17 09:00:00.005000")
        # # tdSql.checkData(6,0,"2018-09-17 09:00:00.006000")
        # # tdSql.checkData(7,0,"2018-09-17 09:00:00.007000")
        # # tdSql.checkData(8,0,"2018-09-17 09:00:00.008000")
        # # tdSql.checkData(9,0,"2018-09-17 09:00:00.009000")
        # # tdSql.query("select timetruncate(ts,1b) from stb_1")
        # # tdSql.checkRows(10)
        # # tdSql.checkData(0,0,"2018-09-17 09:00:00.000000000")
        # # tdSql.checkData(1,0,"2018-09-17 09:00:00.001000000")
        # # tdSql.checkData(2,0,"2018-09-17 09:00:00.002000000")
        # # tdSql.checkData(3,0,"2018-09-17 09:00:00.003000000")
        # # tdSql.checkData(4,0,"2018-09-17 09:00:00.004000000")
        # # tdSql.checkData(5,0,"2018-09-17 09:00:00.005000000")
        # # tdSql.checkData(6,0,"2018-09-17 09:00:00.006000000")
        # # tdSql.checkData(7,0,"2018-09-17 09:00:00.007000000")
        # # tdSql.checkData(8,0,"2018-09-17 09:00:00.008000000")
        # # tdSql.checkData(9,0,"2018-09-17 09:00:00.009000000")
J
add case for max and min  
jiacy-jcy 已提交
345 346 347 348 349 
    def stop(self):
        tdSql.close()
        tdLog.success("%s successfully executed" % __file__)

tdCases.addWindows(__file__, TDTestCase())
G
fix test cases  
Ganlin Zhao 已提交
350 
tdCases.addLinux(__file__, TDTestCase())