Changer le format de date avec une requête MySQL

Salut Thomaseude.
Code:
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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`       integer unsigned  not null auto_increment primary key,
  `chaine`   char(25)          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`chaine`) values
  ('2017-12-26T17:28:35+01:00')
--------------
 
--------------
select * from `test`
--------------
 
+----+---------------------------+
| id | chaine                    |
+----+---------------------------+
|  1 | 2017-12-26T17:28:35+01:00 |
+----+---------------------------+
--------------
alter table `test` add column `val1` datetime null after `chaine`
--------------
 
--------------
alter table `test` add column `val2` date     null after `val1`
--------------
 
--------------
alter table `test` add column `val3` time     null after `val2`
--------------
 
--------------
describe `test`
--------------
 
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| chaine | char(25)         | NO   |     | NULL    |                |
| val1   | datetime         | YES  |     | NULL    |                |
| val2   | date             | YES  |     | NULL    |                |
| val3   | time             | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
--------------
commit
--------------
 
--------------
update `test` set `val1` = date_format(substring(`chaine`,1,19),'%Y-%m-%d %H:%i:%s')
--------------
 
--------------
update `test` set `val2` = date(`val1`)
--------------
 
--------------
update `test` set `val3` = time(`val1`)
--------------
 
--------------
select * from `test`
--------------
 
+----+---------------------------+---------------------+------------+----------+
| id | chaine                    | val1                | val2       | val3     |
+----+---------------------------+---------------------+------------+----------+
|  1 | 2017-12-26T17:28:35+01:00 | 2017-12-26 17:28:35 | 2017-12-26 | 17:28:35 |
+----+---------------------------+---------------------+------------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...