-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathclean_dates_in_db.sql
85 lines (68 loc) · 13.2 KB
/
clean_dates_in_db.sql
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
''' How to detect errors first'''
select * from states where extract(hour from validity_start)!= 0 or extract(minutes from validity_start)!=0 or extract(seconds from validity_start)!=0;
select * from states where extract(hour from validity_end)!= 0 or extract(minutes from validity_end)!=0 or extract(seconds from validity_end)!=0;
select * from state_names where extract(hour from validity_start)!= 0 or extract(minutes from validity_start)!=0 or extract(seconds from validity_start)!=0;
select * from state_names where extract(hour from validity_end)!= 0 or extract(minutes from validity_end)!=0 or extract(seconds from validity_end)!=0;
select * from territories where extract(hour from validity_start)!= 0 or extract(minutes from validity_start)!=0 or extract(seconds from validity_start)!=0;
select * from territories where extract(hour from validity_end)!= 0 or extract(minutes from validity_end)!=0 or extract(seconds from validity_end)!=0;
''' Correct errors '''
update states SET validity_start=validity_start + INTERVAL '43min' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=17;
update state_names SET validity_start=validity_start + INTERVAL '43min' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=17;
update states SET validity_end=validity_end + INTERVAL '43min' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=17;
update state_names SET validity_end=validity_end + INTERVAL '43min' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=17;
update territories SET validity_start=validity_start + INTERVAL '43min' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=17;
update territories SET validity_end=validity_end + INTERVAL '43min' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=17;
update states SET validity_start=validity_start + INTERVAL '49min' + INTERVAL '56sec' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=10 and extract(seconds from validity_start)=04;
update state_names SET validity_start=validity_start + INTERVAL '49min' + INTERVAL '56sec' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=10 and extract(seconds from validity_start)=04;
update states SET validity_end=validity_end + INTERVAL '49min' + INTERVAL '56sec' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=10 and extract(seconds from validity_end)=04;
update state_names SET validity_end=validity_end + INTERVAL '49min' + INTERVAL '56sec' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=10 and extract(seconds from validity_end)=04;
update territories SET validity_start=validity_start + INTERVAL '49min' + INTERVAL '56sec' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=10 and extract(seconds from validity_start)=04;
update territories SET validity_end=validity_end + INTERVAL '49min' + INTERVAL '56sec' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=10 and extract(seconds from validity_end)=04;
update states SET validity_start=validity_start - INTERVAL '7hours' - INTERVAL '19min' - INTERVAL '56sec' where extract(hour from validity_start)=7 and extract(minutes from validity_start)=19 and extract(seconds from validity_start)=56;
update state_names SET validity_start=validity_start - INTERVAL '7hours' - INTERVAL '19min' - INTERVAL '56sec' where extract(hour from validity_start)=7 and extract(minutes from validity_start)=19 and extract(seconds from validity_start)=56;
update states SET validity_end=validity_end - INTERVAL '7hours' - INTERVAL '19min' - INTERVAL '56sec' where extract(hour from validity_end)=7 and extract(minutes from validity_end)=19 and extract(seconds from validity_end)=56;
update state_names SET validity_end=validity_end - INTERVAL '7hours' - INTERVAL '19min' - INTERVAL '56sec' where extract(hour from validity_end)=7 and extract(minutes from validity_end)=19 and extract(seconds from validity_end)=56;
update territories SET validity_start=validity_start - INTERVAL '7hours' - INTERVAL '19min' - INTERVAL '56sec' where extract(hour from validity_start)=7 and extract(minutes from validity_start)=19 and extract(seconds from validity_start)=56;
update territories SET validity_end=validity_end - INTERVAL '7hours' - INTERVAL '19min' - INTERVAL '56sec' where extract(hour from validity_end)=7 and extract(minutes from validity_end)=19 and extract(seconds from validity_end)=56;
update states SET validity_start=validity_start - INTERVAL '11hours' - INTERVAL '49min' - INTERVAL '56sec' where extract(hour from validity_start)=11 and extract(minutes from validity_start)=49 and extract(seconds from validity_start)=56;
update state_names SET validity_start=validity_start - INTERVAL '11hours' - INTERVAL '49min' - INTERVAL '56sec' where extract(hour from validity_start)=11 and extract(minutes from validity_start)=49 and extract(seconds from validity_start)=56;
update states SET validity_end=validity_end - INTERVAL '11hours' - INTERVAL '49min' - INTERVAL '56sec' where extract(hour from validity_end)=11 and extract(minutes from validity_end)=49 and extract(seconds from validity_end)=56;
update state_names SET validity_end=validity_end - INTERVAL '11hours' - INTERVAL '49min' - INTERVAL '56sec' where extract(hour from validity_end)=11 and extract(minutes from validity_end)=49 and extract(seconds from validity_end)=56;
update territories SET validity_start=validity_start - INTERVAL '11hours' - INTERVAL '49min' - INTERVAL '56sec' where extract(hour from validity_start)=11 and extract(minutes from validity_start)=49 and extract(seconds from validity_start)=56;
update territories SET validity_end=validity_end - INTERVAL '11hours' - INTERVAL '49min' - INTERVAL '56sec' where extract(hour from validity_end)=11 and extract(minutes from validity_end)=49 and extract(seconds from validity_end)=56;
update states SET validity_start=validity_start - INTERVAL '2hours' - INTERVAL '44min' - INTERVAL '54sec' where extract(hour from validity_start)=2 and extract(minutes from validity_start)=44 and extract(seconds from validity_start)=54;
update state_names SET validity_start=validity_start - INTERVAL '2hours' - INTERVAL '44min' - INTERVAL '54sec' where extract(hour from validity_start)=2 and extract(minutes from validity_start)=44 and extract(seconds from validity_start)=54;
update states SET validity_end=validity_end - INTERVAL '2hours' - INTERVAL '44min' - INTERVAL '54sec' where extract(hour from validity_end)=2 and extract(minutes from validity_end)=44 and extract(seconds from validity_end)=54;
update state_names SET validity_end=validity_end - INTERVAL '2hours' - INTERVAL '44min' - INTERVAL '54sec' where extract(hour from validity_end)=2 and extract(minutes from validity_end)=44 and extract(seconds from validity_end)=54;
update territories SET validity_start=validity_start - INTERVAL '2hours' - INTERVAL '44min' - INTERVAL '54sec' where extract(hour from validity_start)=2 and extract(minutes from validity_start)=44 and extract(seconds from validity_start)=54;
update territories SET validity_end=validity_end - INTERVAL '2hours' - INTERVAL '44min' - INTERVAL '54sec' where extract(hour from validity_end)=2 and extract(minutes from validity_end)=44 and extract(seconds from validity_end)=54;
update states SET validity_start=validity_start - INTERVAL '12hours' where extract(hour from validity_start)=12 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update state_names SET validity_start=validity_start - INTERVAL '12hours' where extract(hour from validity_start)=12 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update states SET validity_end=validity_end - INTERVAL '12hours' where extract(hour from validity_end)=12 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update state_names SET validity_end=validity_end - INTERVAL '12hours' where extract(hour from validity_end)=12 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update territories SET validity_start=validity_start - INTERVAL '12hours' where extract(hour from validity_start)=12 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update territories SET validity_end=validity_end - INTERVAL '12hours' where extract(hour from validity_end)=12 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update states SET validity_start=validity_start + INTERVAL '21min' + INTERVAL '30sec' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=38 and extract(seconds from validity_start)=30;
update state_names SET validity_start=validity_start + INTERVAL '21min' + INTERVAL '30sec' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=38 and extract(seconds from validity_start)=30;
update states SET validity_end=validity_end + INTERVAL '21min' + INTERVAL '30sec' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=38 and extract(seconds from validity_end)=30;
update state_names SET validity_end=validity_end + INTERVAL '21min' + INTERVAL '30sec' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=38 and extract(seconds from validity_end)=30;
update territories SET validity_start=validity_start + INTERVAL '21min' + INTERVAL '30sec' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=38 and extract(seconds from validity_start)=30;
update territories SET validity_end=validity_end + INTERVAL '21min' + INTERVAL '30sec' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=38 and extract(seconds from validity_end)=30;
update states SET validity_start=validity_start + INTERVAL '1hours' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update state_names SET validity_start=validity_start + INTERVAL '1hours' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update states SET validity_end=validity_end + INTERVAL '1hours' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update state_names SET validity_end=validity_end + INTERVAL '1hours' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update territories SET validity_start=validity_start + INTERVAL '1hours' where extract(hour from validity_start)=23 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update territories SET validity_end=validity_end + INTERVAL '1hours' where extract(hour from validity_end)=23 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update states SET validity_start=validity_start + INTERVAL '2hours' where extract(hour from validity_start)=22 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update state_names SET validity_start=validity_start + INTERVAL '2hours' where extract(hour from validity_start)=22 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update states SET validity_end=validity_end + INTERVAL '2hours' where extract(hour from validity_end)=22 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update state_names SET validity_end=validity_end + INTERVAL '2hours' where extract(hour from validity_end)=22 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update territories SET validity_start=validity_start + INTERVAL '2hours' where extract(hour from validity_start)=22 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update territories SET validity_end=validity_end + INTERVAL '2hours' where extract(hour from validity_end)=22 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update states SET validity_start=validity_start + INTERVAL '6hours' where extract(hour from validity_start)=18 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update state_names SET validity_start=validity_start + INTERVAL '6hours' where extract(hour from validity_start)=18 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update states SET validity_end=validity_end + INTERVAL '6hours' where extract(hour from validity_end)=18 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update state_names SET validity_end=validity_end + INTERVAL '6hours' where extract(hour from validity_end)=18 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;
update territories SET validity_start=validity_start + INTERVAL '6hours' where extract(hour from validity_start)=18 and extract(minutes from validity_start)=0 and extract(seconds from validity_start)=0;
update territories SET validity_end=validity_end + INTERVAL '6hours' where extract(hour from validity_end)=18 and extract(minutes from validity_end)=0 and extract(seconds from validity_end)=0;