repl.it
@Gokujo/

datetime vs varchar

PHP (Legacy)

No description

fork
loading
main.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
/**
Сбор данных происходил при помощи python 3. Вот скрипт:

import psycopg2, datetime, json
from datetime import timedelta
host = '*******'
user = '*******'
pw = '********'
db = '********'
conn = psycopg2.connect(host=host, database=db, user=user, password=pw)
cur = conn.cursor()

data = {'timestamp': [], 'timetext': [], 'varchar': [], 'vardate': []}
dtForm = '%Y-%m-%d %H:%M:%S.%f'

def count(dataIN, times, befehl = 'name'):
...    dtForm = '%Y-%m-%d %H:%M:%S.%f'
...    vStart = datetime.datetime.now()
...    print("Vorgangstart: " + str(datetime.datetime.strftime(vStart, dtForm)))
...    query = "select * from hr_attendance where {} between '2018-01-01' and '2018-12-31'".format(befehl)
...    print("Befehl: {}".format(query))
...    for i in range (0, times):
...        #print("Runde: " + str((i+1)))
...        now = datetime.datetime.now()
...        #print("Start: " + str(datetime.datetime.strftime(now, dtForm)))
...        cur.execute(query)
...        after = datetime.datetime.now()
...        diff = after - now
...        if dataIN == 'timestamp':
...            data['timestamp'].append(diff.microseconds)
...        elif dataIN == 'timetext':
...            data['timetext'].append(diff.microseconds)
...        elif dataIN == 'varchar':
...            data['varchar'].append(diff.microseconds)
...        elif dataIN == 'vardate':
...            data['vardate'].append(diff.microseconds)
...        #print("Ende: " + str(datetime.datetime.strftime(after, dtForm)))
...    print("Vorgangende: " + str(datetime.datetime.strftime(datetime.datetime.now(), dtForm)))
...    vDiff = datetime.datetime.now() - vStart
...    print("Dauer: {}s".format(vDiff.seconds))
...    print("Zeilenanzahl: 36200")

Input: count('timestamp', 100)
Output: Vorgangstart: 2019-12-16 09:24:28.200001
Befehl: select * from hr_attendance where name between '2018-01-01' and '2018-12-31'
Vorgangende: 2019-12-16 09:24:35.416925
Dauer: 7s
Zeilenanzahl: 36200

Input: count('timetext', 100, 'name::text')
Output: Vorgangstart: 2019-12-16 09:25:01.592632
Befehl: select * from hr_attendance where name::text between '2018-01-01' and '2018-12-31'
Vorgangende: 2019-12-16 09:25:07.393066
Dauer: 5s
Zeilenanzahl: 36200

Input: count('varchar', 100)
Output: Vorgangstart: 2019-12-16 09:30:16.972253
Befehl: select * from hr_attendance where name between '2018-01-01' and '2018-12-31'
Vorgangende: 2019-12-16 09:30:24.476113
Dauer: 7s
Zeilenanzahl: 36200

Input: count('vardate', 100, 'date(name)')
Output: Vorgangstart: 2019-12-16 09:30:41.431898
Befehl: select * from hr_attendance where date(name) between '2018-01-01' and '2018-12-31'
Vorgangende: 2019-12-16 09:31:04.423893
Dauer: 22s
Zeilenanzahl: 36200
*/

$data = '{"timestamp": [116009, 100271, 81182, 61107, 50128, 67092, 80176, 40066, 40122, 80240, 83338, 79270, 80443, 62226, 73036, 80092, 80330, 58082, 71085, 61068, 45997, 63060, 52002, 55999, 65116, 87190, 60050, 80060, 80162, 45180, 34999, 59225, 120424, 106279, 42061, 43000, 43112, 53107, 31997, 39115, 36057, 48240, 75111, 77278, 83116, 100115, 110216, 112427, 85197, 92198, 80005, 80125, 55211, 74991, 53999, 57233, 57057, 82161, 78997, 61062, 68191, 68056, 66123, 38042, 26033, 25080, 24060, 25997, 764045, 82131, 72115, 79303, 80084, 60001, 80126, 67015, 70127, 53014, 53008, 61279, 54997, 50060, 50113, 60122, 60000, 80223, 67236, 56109, 50060, 74057, 53222, 52252, 52059, 49168, 71136, 54188, 60171, 80172, 60237, 61228], "timetext": [58001, 63003, 60146, 59995, 49164, 59054, 52999, 58043, 47057, 62119, 60057, 50008, 61108, 100294, 60025, 50133, 50217, 47061, 43008, 60099, 61229, 49228, 59059, 60055, 51139, 70227, 52234, 32169, 39998, 48181, 53049, 72302, 60001, 60058, 54107, 42059, 51160, 62188, 67062, 45027, 52095, 44998, 23995, 23062, 24068, 24058, 22997, 25030, 54084, 61109, 58153, 89060, 75004, 60176, 59227, 59164, 83119, 120013, 111277, 92216, 75186, 63121, 68089, 93244, 72988, 80243, 80170, 59110, 46120, 50055, 56331, 64109, 43117, 45001, 53057, 53052, 56035, 59006, 64294, 46051, 66063, 49001, 69181, 80142, 60141, 70244, 74161, 52121, 44103, 58127, 41049, 56002, 52996, 71000, 60005, 60233, 39120, 64164, 56062, 40109], "varchar": [60995, 51000, 73217, 60117, 79077, 73075, 66060, 60174, 100157, 80126, 80120, 59990, 60007, 102121, 117056, 120121, 80058, 139997, 141231, 119061, 64294, 74160, 50066, 71080, 62060, 77987, 60128, 100072, 120233, 79242, 60145, 80203, 79997, 80273, 100082, 63109, 54997, 46042, 41996, 49183, 55993, 68121, 60103, 59999, 80061, 80115, 100222, 120285, 119159, 80184, 120108, 140102, 170173, 95188, 90190, 75122, 62111, 71306, 76100, 81225, 77998, 48031, 72111, 80116, 80111, 59999, 100064, 63058, 63143, 54200, 59230, 37056, 58171, 26073, 35000, 30999, 25172, 88074, 80093, 72295, 87120, 100177, 67182, 77163, 59064, 60062, 66122, 70015, 60084, 100192, 62043, 49055, 66073, 42990, 54003, 52131, 63120, 70998, 79113, 60183], "vardate": [218152, 244524, 240056, 226663, 208631, 264519, 224455, 235366, 223286, 232177, 244278, 194322, 221505, 188179, 215994, 199996, 209996, 204997, 222993, 241998, 227995, 244141, 231200, 257158, 200118, 208171, 226241, 205206, 240141, 239366, 140114, 252307, 214400, 213288, 237470, 242341, 219341, 221296, 222492, 217520, 238368, 211178, 250142, 212121, 194405, 238343, 174316, 227164, 296767, 309461, 345310, 240511, 215307, 212358, 232318, 187247, 157496, 232257, 322485, 289343, 249309, 227303, 175239, 219223, 257367, 229237, 254474, 238227, 200367, 368188, 331965, 279500, 220178, 206260, 207367, 206224, 228347, 219081, 221101, 161397, 250103, 180223, 240326, 343174, 316424, 235354, 209349, 197052, 198215, 220318, 221389, 206011, 185237, 226373, 240115, 245220, 219490, 255469, 226308, 165200]}';
$data = json_decode($data, true);

function printData($format, $start, $ende, $dauer, $befehl, $daten) {
	echo "============================================\n";
	echo "Дата как {$format}\n";
	echo "Запрос: {$befehl}\n";
	echo "Старт: {$start}\n";
	echo "Финиш: {$ende}\n";
	echo "Продолжительность: {$dauer}\n\n";

	$count_data = count($daten);
	echo "Всего данных: " . count($daten) . "\n";
	echo "Самое меньшее время: " . min($daten) . "ms\n";
	echo "Самое большее время: " . max($daten) . "ms\n";

	$avg = 0;
	$allms = 0;
	foreach($daten as $time) $allms += $time;
	$avg = $allms / $count_data;
	echo "В среднем затрачено времени: {$avg}ms";


	echo "\n============================================\n\n\n";
}

printData('datetime/timestamp', '2019-12-16 09:23:58.777001', '2019-12-16 09:24:05.057548', '7s', "select * from hr_attendance where name between '2018-01-01' and '2018-12-31'", $data['timestamp']);
printData('datetime/timestamp', '2019-12-16 09:25:01.592632', '2019-12-16 09:25:07.393066', '5s', "select * from hr_attendance where name::text between '2018-01-01' and '2018-12-31'", $data['timetext']);
printData('varchar', '2019-12-16 09:30:16.972253', '2019-12-16 09:30:24.476113', '7s', "select * from hr_attendance where name between '2018-01-01' and '2018-12-31'", $data['varchar']);
printData('varchar', '2019-12-16 09:30:41.431898', '2019-12-16 09:31:04.423893', '22s', "select * from hr_attendance where date(name) between '2018-01-01' and '2018-12-31'", $data['vardate']);
PHP 7.0.8 (Legacy: use the following for new features: Command-line PHP: https://repl.it/languages/php_cli PHP Web Server: https://repl.it/languages/php7
?