WEBVTT 1 00:00:00.200 --> 00:00:01.840 Hi Guys, and welcome back. 2 00:00:01.840 --> 00:00:03.410 In this video, we're going to look 3 00:00:03.410 --> 00:00:06.570 at how we can save date and times to Postgres. 4 00:00:06.570 --> 00:00:09.460 While adhering to the best practises that we've covered. 5 00:00:09.460 --> 00:00:11.810 We're not going to be working in our polling app in 6 00:00:11.810 --> 00:00:14.210 this video, just a test file for now, 7 00:00:14.210 --> 00:00:16.080 but we're going to include this knowledge 8 00:00:16.080 --> 00:00:18.140 into our polling app in the next video. 9 00:00:18.140 --> 00:00:19.223 So let's get to it. 10 00:00:20.170 --> 00:00:23.620 I'm going to start off by creating a file here in 11 00:00:23.620 --> 00:00:25.360 the project just anywhere, it doesn't matter. 12 00:00:25.360 --> 00:00:26.730 We're gonna delete it at the end. 13 00:00:26.730 --> 00:00:29.910 And I'm gonna call it dates.py for example. 14 00:00:29.910 --> 00:00:31.910 Just make sure not to call it date time 15 00:00:31.910 --> 00:00:33.220 because that's gonna conflict 16 00:00:33.220 --> 00:00:36.010 with the date time module when we try to import it. 17 00:00:36.010 --> 00:00:39.770 Like we've mentioned, we recommend getting the users date in 18 00:00:39.770 --> 00:00:44.480 their local time using pytz converted to UTC 19 00:00:44.480 --> 00:00:47.730 and then store it in your database in UTC. 20 00:00:47.730 --> 00:00:50.210 When you're displaying dates, get the UTC, 21 00:00:50.210 --> 00:00:51.590 get the user's timezone, 22 00:00:51.590 --> 00:00:55.070 convert the UTC to their timezone and display it to them. 23 00:00:55.070 --> 00:00:57.620 Remember that users cannot change time zones. 24 00:00:57.620 --> 00:01:00.010 So the best way to get a reliable time zone 25 00:01:00.010 --> 00:01:02.500 from them is by asking them. 26 00:01:02.500 --> 00:01:03.720 So here's what we're gonna do, 27 00:01:03.720 --> 00:01:05.960 we're gonna do user timezone equal 28 00:01:05.960 --> 00:01:08.430 and we're gonna ask them for their timezone. 29 00:01:08.430 --> 00:01:10.140 Applications will usually ask 30 00:01:10.140 --> 00:01:13.330 this if especially they can't tell what times 31 00:01:13.330 --> 00:01:15.910 on your computers in, although often they'll use 32 00:01:15.910 --> 00:01:19.280 the computers local timezone, and that's also okay. 33 00:01:19.280 --> 00:01:21.617 We're going to import pytz as well as 34 00:01:21.617 --> 00:01:23.300 the JSON module because we're gonna 35 00:01:23.300 --> 00:01:26.090 be saving this timezone to a config file. 36 00:01:26.090 --> 00:01:27.900 So what we're gonna do is we're gonna try 37 00:01:27.900 --> 00:01:31.550 to do a pytz of timezone of a user's timezone, 38 00:01:31.550 --> 00:01:33.450 make sure that it is a valid one. 39 00:01:33.450 --> 00:01:37.680 And if it isn't, then it's gonna raise a pytz exceptions, 40 00:01:37.680 --> 00:01:41.220 unknown timezone error. 41 00:01:41.220 --> 00:01:42.680 And if that does happen, 42 00:01:42.680 --> 00:01:45.280 then we're gonna print that was not a valid timezone 43 00:01:46.210 --> 00:01:49.700 and we're going to raise to crash the programme. 44 00:01:49.700 --> 00:01:50.810 If we don't do that 45 00:01:50.810 --> 00:01:53.430 then we're gonna do with open of 46 00:01:53.430 --> 00:01:56.870 the userconfig.JSONfile in write mode, 47 00:01:56.870 --> 00:02:00.040 and then as config we can write this to a 48 00:02:00.040 --> 00:02:05.040 file so we can do JSON.dump timezone is user timezone 49 00:02:05.590 --> 00:02:08.990 and make sure to put the config file in there to write to. 50 00:02:08.990 --> 00:02:11.350 Notice that I don't have pytz installed. 51 00:02:11.350 --> 00:02:13.740 So we're gonna do that I've opened up 52 00:02:13.740 --> 00:02:16.410 the PI charm project interpreter settings here 53 00:02:16.410 --> 00:02:18.110 we can press the plus icon. 54 00:02:18.110 --> 00:02:21.200 Find pytz and instal it make sure 55 00:02:21.200 --> 00:02:22.870 to select the correct one. 56 00:02:22.870 --> 00:02:24.270 Once that's successfully installed, 57 00:02:24.270 --> 00:02:27.740 we can close all of this and we have pytz installed, 58 00:02:27.740 --> 00:02:30.883 we can now right click this file and run it. 59 00:02:31.730 --> 00:02:33.360 Then it's gonna ask us for our timezone 60 00:02:33.360 --> 00:02:36.183 and I'm gonna enter Europe/London. 61 00:02:37.070 --> 00:02:39.290 And that's it timezone to a file. 62 00:02:39.290 --> 00:02:41.420 So I'm gonna open that and you can see we've got a JSON 63 00:02:41.420 --> 00:02:44.720 file here with my user configuration. 64 00:02:44.720 --> 00:02:47.210 I recommend that you do ask the user 65 00:02:47.210 --> 00:02:50.340 for their timezone when you start the application. 66 00:02:50.340 --> 00:02:53.580 And you can use pytz to display all of 67 00:02:53.580 --> 00:02:54.790 the available timezone so 68 00:02:54.790 --> 00:02:57.300 that your user can pick one of those. 69 00:02:57.300 --> 00:02:59.010 You could also have timezone information 70 00:02:59.010 --> 00:03:01.920 related to your users in your database, 71 00:03:01.920 --> 00:03:04.750 so that you have that stored there for whenever you want 72 00:03:04.750 --> 00:03:06.710 to show some dates to them. 73 00:03:06.710 --> 00:03:09.320 All right, I'm gonna go ahead and delete this config file 74 00:03:09.320 --> 00:03:12.920 and all the contents in this file. 75 00:03:12.920 --> 00:03:15.510 And we're gonna look at how you can save a date to 76 00:03:15.510 --> 00:03:19.480 the database using pytz and psychopg2. 77 00:03:19.480 --> 00:03:21.710 The first thing you may want to do is of course, 78 00:03:21.710 --> 00:03:22.920 import the stuff you need. 79 00:03:22.920 --> 00:03:27.920 So datetime, we need pytz, and we need psychopg2, 80 00:03:28.060 --> 00:03:29.590 then we need a connection. 81 00:03:29.590 --> 00:03:33.603 So we're gonna do .m.importload.m. 82 00:03:34.440 --> 00:03:36.520 And we're gonna load that. 83 00:03:36.520 --> 00:03:38.050 And then we're gonna get up the connection 84 00:03:38.050 --> 00:03:43.050 which is psychopg2.connect with os.environ.getofdatabaseURI 85 00:03:46.170 --> 00:03:48.160 and then we're gonna grab the user timezone. 86 00:03:48.160 --> 00:03:50.390 Again, this might be coming from a config file 87 00:03:50.390 --> 00:03:52.670 that you can read or it might be coming from a database 88 00:03:52.670 --> 00:03:57.300 for now I'm just gonna hard code it as Europe, London, 89 00:03:57.300 --> 00:03:59.060 we do have to import OS as well. 90 00:03:59.060 --> 00:04:00.910 So I'm gonna do that at the top. 91 00:04:00.910 --> 00:04:03.470 Then let's say that our application is saving entries 92 00:04:03.470 --> 00:04:06.120 of a programming diary as we did earlier on in the course, 93 00:04:06.120 --> 00:04:07.730 we can do new posts content, 94 00:04:07.730 --> 00:04:09.993 and we can ask the user for something. 95 00:04:11.100 --> 00:04:13.710 And now that we've got this we want to get 96 00:04:13.710 --> 00:04:16.210 the current date we've learned how to do this already 97 00:04:16.210 --> 00:04:18.460 we can do new post date is equal 98 00:04:18.460 --> 00:04:20.730 to user timezone.localize. 99 00:04:20.730 --> 00:04:23.470 And here we put the current time. 100 00:04:23.470 --> 00:04:28.470 This is going to turn the current naive datetime object 101 00:04:28.520 --> 00:04:32.580 into the users timezone, but without changing the date 102 00:04:32.580 --> 00:04:34.050 or time of it, so it's just gonna 103 00:04:34.050 --> 00:04:36.790 add timezone information to it. 104 00:04:36.790 --> 00:04:39.860 Then we're going to use that to convert it to UTC. 105 00:04:39.860 --> 00:04:42.600 So we'll say UTC post date is equal 106 00:04:42.600 --> 00:04:47.097 to new post date as timezone pytz.utc. 107 00:04:49.360 --> 00:04:53.540 Then as usual with connection with connection.cursor 108 00:04:53.540 --> 00:04:56.650 as cursor, we can do something like cursor execute, 109 00:04:56.650 --> 00:04:59.330 and potentially add an insert into posts, 110 00:04:59.330 --> 00:05:03.950 content and date the values of percent s percent s. 111 00:05:03.950 --> 00:05:07.340 And what we would want to insert into here is 112 00:05:07.340 --> 00:05:08.900 the new post content in this case, 113 00:05:08.900 --> 00:05:10.640 and then the timestamp so it will be 114 00:05:10.640 --> 00:05:14.060 the UTC post date.timestamp. 115 00:05:14.060 --> 00:05:16.800 And that would make sure to insert the string into 116 00:05:16.800 --> 00:05:19.110 the content column and this integer 117 00:05:19.110 --> 00:05:20.610 or this floating point value really 118 00:05:20.610 --> 00:05:23.160 because timestamps do have a floating point number 119 00:05:23.160 --> 00:05:24.233 into the date. 120 00:05:25.110 --> 00:05:27.450 All right, so this is how I would recommend 121 00:05:27.450 --> 00:05:30.640 you handle saving dates into Postgres. 122 00:05:30.640 --> 00:05:33.920 First, we need the user's timezone from a config file 123 00:05:33.920 --> 00:05:35.320 or from the database. 124 00:05:35.320 --> 00:05:37.860 Then we need whatever we wanna save as well as 125 00:05:37.860 --> 00:05:41.670 the date we can get the date by calculating the now date 126 00:05:41.670 --> 00:05:45.220 and using pytz to localise it into the user's timezone. 127 00:05:45.220 --> 00:05:46.137 This doesn't change the date 128 00:05:46.137 --> 00:05:49.320 and time it just adds timezone information. 129 00:05:49.320 --> 00:05:53.240 Then using as timezone with pytz.utc 130 00:05:53.240 --> 00:05:55.500 we can convert that into the new date. 131 00:05:55.500 --> 00:05:59.360 That is what we save as a timestamp into the column. 132 00:05:59.360 --> 00:06:01.360 When we're reading data from the database, 133 00:06:01.360 --> 00:06:02.930 we must remember that this timestamp 134 00:06:02.930 --> 00:06:07.110 that we saved represents current time in UTC. 135 00:06:07.110 --> 00:06:09.760 So let's have a look at how we can go the other way 136 00:06:09.760 --> 00:06:12.233 and read data from the database. 137 00:06:14.350 --> 00:06:15.620 I've deleted some of the code here, 138 00:06:15.620 --> 00:06:17.010 but much of it is still the same. 139 00:06:17.010 --> 00:06:19.190 We're still connecting to the database, 140 00:06:19.190 --> 00:06:20.810 we've got the user's timezone, 141 00:06:20.810 --> 00:06:23.680 and now here we're selecting star from posts. 142 00:06:23.680 --> 00:06:27.580 For example, here we have the content and the date, 143 00:06:27.580 --> 00:06:29.760 which is a UTC timestamp. 144 00:06:29.760 --> 00:06:31.800 So what we would do normally is something like 145 00:06:31.800 --> 00:06:34.563 for posting cursor, for example. 146 00:06:35.550 --> 00:06:37.970 And then we can do underscore ID, 147 00:06:37.970 --> 00:06:41.740 content and timestamp equal post to the structure 148 00:06:41.740 --> 00:06:44.810 the post tuple into the three variables. 149 00:06:44.810 --> 00:06:48.570 And now what we have to do is read the timestamp as if 150 00:06:48.570 --> 00:06:50.770 it was a UTC timestamp. 151 00:06:50.770 --> 00:06:52.830 So we can do naive daytime, for example. 152 00:06:52.830 --> 00:06:55.970 Equals date time.from timestamp 153 00:06:55.970 --> 00:06:58.010 and putting the timestamp in there. 154 00:06:58.010 --> 00:06:59.630 If we do this though, oh sorry, 155 00:06:59.630 --> 00:07:02.930 this has to be datetime.from timestamp. 156 00:07:02.930 --> 00:07:04.960 If we do this though, it's going to assume that 157 00:07:04.960 --> 00:07:09.830 the timestamp we're reading is a timestamp in local time, 158 00:07:09.830 --> 00:07:12.110 and not in UTC time. 159 00:07:12.110 --> 00:07:14.545 So it's very important when you're reading UTC timestamps 160 00:07:14.545 --> 00:07:17.950 that you type UTC from timestamp. 161 00:07:17.950 --> 00:07:22.950 Then we can get the UTC date by saying pytz.utc.localize 162 00:07:23.580 --> 00:07:26.120 and pass in the naive daytime 163 00:07:26.120 --> 00:07:28.260 and then we can convert that to local date 164 00:07:28.260 --> 00:07:32.290 using UTC date.astimezone, with a user's timezone. 165 00:07:32.290 --> 00:07:34.450 Finally, we can put into the local date 166 00:07:34.450 --> 00:07:36.900 if you want as well as the content. 167 00:07:36.900 --> 00:07:40.090 Well this is going to do is it's going to grab 168 00:07:40.090 --> 00:07:43.820 the timestamp loaded into a date time object as 169 00:07:43.820 --> 00:07:47.360 if it was UTC but without timezone information. 170 00:07:47.360 --> 00:07:50.550 Then using pytz.utc.localize, 171 00:07:50.550 --> 00:07:52.070 where adding timezone information, 172 00:07:52.070 --> 00:07:54.600 without changing its contents. 173 00:07:54.600 --> 00:07:58.770 And finally, using UTC date as timezone we are converting 174 00:07:58.770 --> 00:08:00.620 that date into the new timezone. 175 00:08:00.620 --> 00:08:02.800 And this takes care of things like daylight savings, 176 00:08:02.800 --> 00:08:06.550 and so on, which are so very difficult to deal with. 177 00:08:06.550 --> 00:08:09.120 All right, so hopefully these three snippets, 178 00:08:09.120 --> 00:08:12.970 the config file, loading and saving into a database do help. 179 00:08:12.970 --> 00:08:14.520 I hope you've learned something in this video, 180 00:08:14.520 --> 00:08:17.680 we're gonna make use of this now in our polling project. 181 00:08:17.680 --> 00:08:19.883 So what I'm gonna do is delete dates.py, 182 00:08:21.650 --> 00:08:24.100 and we can go back to our project in the next video. 183 00:08:24.100 --> 00:08:25.050 I'll see you there.