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 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
| --------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `t1`
--------------
--------------
CREATE TABLE `t1`
( `the_decimal` decimal(15,3) NOT NULL PRIMARY KEY,
`the_value` float NOT NULL,
`the_time` timestamp(3) generated always AS (FROM_UNIXTIME( the_decimal )) virtual NOT NULL,
`the_time_05` timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/ 5)* 5)) virtual NOT NULL,
`the_time_15` timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/15)*15)) virtual NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `t1` (`the_decimal`,`the_value`) values
(unix_timestamp('2019-02-12 10:20:00.123'),100.44),
(unix_timestamp('2019-02-12 10:20:01.123'),102.44),
(unix_timestamp('2019-02-12 10:20:02.123'),104.44),
(unix_timestamp('2019-02-12 10:20:03.123'),106.44),
(unix_timestamp('2019-02-12 10:20:04.123'),108.44),
(unix_timestamp('2019-02-12 10:20:05.123'),110.44),
(unix_timestamp('2019-02-12 10:20:06.123'),112.44),
(unix_timestamp('2019-02-12 10:20:07.123'),114.44),
(unix_timestamp('2019-02-12 10:20:08.123'),116.44),
(unix_timestamp('2019-02-12 10:20:09.123'),118.44),
(unix_timestamp('2019-02-12 10:20:10.123'),120.44),
(unix_timestamp('2019-02-12 10:20:11.123'),122.44),
(unix_timestamp('2019-02-12 10:20:12.123'),124.44),
(unix_timestamp('2019-02-12 10:20:13.123'),126.44),
(unix_timestamp('2019-02-12 10:20:14.123'),128.44),
(unix_timestamp('2019-02-12 10:20:15.123'),130.44),
(unix_timestamp('2019-02-12 10:20:16.123'),132.44),
(unix_timestamp('2019-02-12 10:20:17.123'),134.44),
(unix_timestamp('2019-02-12 10:20:18.123'),136.44),
(unix_timestamp('2019-02-12 10:20:19.123'),138.44),
(unix_timestamp('2019-02-12 10:20:20.123'),140.44),
(unix_timestamp('2019-02-12 10:20:21.123'),142.44),
(unix_timestamp('2019-02-12 10:20:22.123'),144.44),
(unix_timestamp('2019-02-12 10:20:23.123'),146.44),
(unix_timestamp('2019-02-12 10:20:24.123'),148.44),
(unix_timestamp('2019-02-12 10:20:25.123'),150.44),
(unix_timestamp('2019-02-12 10:20:26.123'),152.44),
(unix_timestamp('2019-02-12 10:20:27.123'),154.44),
(unix_timestamp('2019-02-12 10:20:28.123'),156.44),
(unix_timestamp('2019-02-12 10:20:29.123'),158.44),
(unix_timestamp('2019-02-12 10:20:30.123'),160.44),
(unix_timestamp('2019-02-12 10:20:31.123'),162.44),
(unix_timestamp('2019-02-12 10:20:32.123'),164.44),
(unix_timestamp('2019-02-12 10:20:33.123'),166.44),
(unix_timestamp('2019-02-12 10:20:34.123'),168.44),
(unix_timestamp('2019-02-12 10:20:35.123'),170.44),
(unix_timestamp('2019-02-12 10:20:36.123'),172.44),
(unix_timestamp('2019-02-12 10:20:37.123'),174.44),
(unix_timestamp('2019-02-12 10:20:38.123'),176.44),
(unix_timestamp('2019-02-12 10:20:39.123'),178.44)
--------------
--------------
select * from `t1`
--------------
+----------------+-----------+-------------------------+---------------------+---------------------+
| the_decimal | the_value | the_time | the_time_05 | the_time_15 |
+----------------+-----------+-------------------------+---------------------+---------------------+
| 1549963200.123 | 100.44 | 2019-02-12 10:20:00.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963201.123 | 102.44 | 2019-02-12 10:20:01.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963202.123 | 104.44 | 2019-02-12 10:20:02.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963203.123 | 106.44 | 2019-02-12 10:20:03.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963204.123 | 108.44 | 2019-02-12 10:20:04.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963205.123 | 110.44 | 2019-02-12 10:20:05.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963206.123 | 112.44 | 2019-02-12 10:20:06.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963207.123 | 114.44 | 2019-02-12 10:20:07.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963208.123 | 116.44 | 2019-02-12 10:20:08.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963209.123 | 118.44 | 2019-02-12 10:20:09.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963210.123 | 120.44 | 2019-02-12 10:20:10.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963211.123 | 122.44 | 2019-02-12 10:20:11.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963212.123 | 124.44 | 2019-02-12 10:20:12.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963213.123 | 126.44 | 2019-02-12 10:20:13.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963214.123 | 128.44 | 2019-02-12 10:20:14.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963215.123 | 130.44 | 2019-02-12 10:20:15.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963216.123 | 132.44 | 2019-02-12 10:20:16.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963217.123 | 134.44 | 2019-02-12 10:20:17.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963218.123 | 136.44 | 2019-02-12 10:20:18.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963219.123 | 138.44 | 2019-02-12 10:20:19.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963220.123 | 140.44 | 2019-02-12 10:20:20.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963221.123 | 142.44 | 2019-02-12 10:20:21.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963222.123 | 144.44 | 2019-02-12 10:20:22.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963223.123 | 146.44 | 2019-02-12 10:20:23.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963224.123 | 148.44 | 2019-02-12 10:20:24.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963225.123 | 150.44 | 2019-02-12 10:20:25.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963226.123 | 152.44 | 2019-02-12 10:20:26.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963227.123 | 154.44 | 2019-02-12 10:20:27.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963228.123 | 156.44 | 2019-02-12 10:20:28.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963229.123 | 158.44 | 2019-02-12 10:20:29.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963230.123 | 160.44 | 2019-02-12 10:20:30.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963231.123 | 162.44 | 2019-02-12 10:20:31.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963232.123 | 164.44 | 2019-02-12 10:20:32.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963233.123 | 166.44 | 2019-02-12 10:20:33.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963234.123 | 168.44 | 2019-02-12 10:20:34.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963235.123 | 170.44 | 2019-02-12 10:20:35.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963236.123 | 172.44 | 2019-02-12 10:20:36.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963237.123 | 174.44 | 2019-02-12 10:20:37.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963238.123 | 176.44 | 2019-02-12 10:20:38.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963239.123 | 178.44 | 2019-02-12 10:20:39.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
+----------------+-----------+-------------------------+---------------------+---------------------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |