Jose: Hi Guys, and welcome back.
In this video, we're going to look
at how we can save date and times to Postgres.
While adhering to the best practises that we've covered.
We're not going to be working in our polling app in
this video, just a test file for now,
but we're going to include this knowledge
into our polling app in the next video.
So let's get to it.
I'm going to start off by creating a file here in
the project just anywhere, it doesn't matter.
We're gonna delete it at the end.
And I'm gonna call it dates.py for example.
Just make sure not to call it date time
because that's gonna conflict
with the date time module when we try to import it.
Like we've mentioned, we recommend getting the users date in
their local time using pytz converted to UTC
and then store it in your database in UTC.
When you're displaying dates, get the UTC,
get the user's timezone,
convert the UTC to their timezone and display it to them.
Remember that users cannot change time zones.
So the best way to get a reliable time zone
from them is by asking them.
So here's what we're gonna do,
we're gonna do user timezone equal
and we're gonna ask them for their timezone.
Applications will usually ask
this if especially they can't tell what times
on your computers in, although often they'll use
the computers local timezone, and that's also okay.
We're going to import pytz as well as
the JSON module because we're gonna
be saving this timezone to a config file.
So what we're gonna do is we're gonna try
to do a pytz of timezone of a user's timezone,
make sure that it is a valid one.
And if it isn't, then it's gonna raise a pytz exceptions,
unknown timezone error.
And if that does happen,
then we're gonna print that was not a valid timezone
and we're going to raise to crash the programme.
If we don't do that
then we're gonna do with open of
the userconfig.JSONfile in write mode,
and then as config we can write this to a
file so we can do JSON.dump timezone is user timezone
and make sure to put the config file in there to write to.
Notice that I don't have pytz installed.
So we're gonna do that I've opened up
the PI charm project interpreter settings here
we can press the plus icon.
Find pytz and instal it make sure
to select the correct one.
Once that's successfully installed,
we can close all of this and we have pytz installed,
we can now right click this file and run it.
Then it's gonna ask us for our timezone
and I'm gonna enter Europe/London.
And that's it timezone to a file.
So I'm gonna open that and you can see we've got a JSON
file here with my user configuration.
I recommend that you do ask the user
for their timezone when you start the application.
And you can use pytz to display all of
the available timezone so
that your user can pick one of those.
You could also have timezone information
related to your users in your database,
so that you have that stored there for whenever you want
to show some dates to them.
All right, I'm gonna go ahead and delete this config file
and all the contents in this file.
And we're gonna look at how you can save a date to
the database using pytz and psychopg2.
The first thing you may want to do is of course,
import the stuff you need.
So datetime, we need pytz, and we need psychopg2,
then we need a connection.
So we're gonna do .m.importload.m.
And we're gonna load that.
And then we're gonna get up the connection
which is psychopg2.connect with os.environ.getofdatabaseURI
and then we're gonna grab the user timezone.
Again, this might be coming from a config file
that you can read or it might be coming from a database
for now I'm just gonna hard code it as Europe, London,
we do have to import OS as well.
So I'm gonna do that at the top.
Then let's say that our application is saving entries
of a programming diary as we did earlier on in the course,
we can do new posts content,
and we can ask the user for something.
And now that we've got this we want to get
the current date we've learned how to do this already
we can do new post date is equal
to user timezone.localize.
And here we put the current time.
This is going to turn the current naive datetime object
into the users timezone, but without changing the date
or time of it, so it's just gonna
add timezone information to it.
Then we're going to use that to convert it to UTC.
So we'll say UTC post date is equal
to new post date as timezone pytz.utc.
Then as usual with connection with connection.cursor
as cursor, we can do something like cursor execute,
and potentially add an insert into posts,
content and date the values of percent s percent s.
And what we would want to insert into here is
the new post content in this case,
and then the timestamp so it will be
the UTC post date.timestamp.
And that would make sure to insert the string into
the content column and this integer
or this floating point value really
because timestamps do have a floating point number
into the date.
All right, so this is how I would recommend
you handle saving dates into Postgres.
First, we need the user's timezone from a config file
or from the database.
Then we need whatever we wanna save as well as
the date we can get the date by calculating the now date
and using pytz to localise it into the user's timezone.
This doesn't change the date
and time it just adds timezone information.
Then using as timezone with pytz.utc
we can convert that into the new date.
That is what we save as a timestamp into the column.
When we're reading data from the database,
we must remember that this timestamp
that we saved represents current time in UTC.
So let's have a look at how we can go the other way
and read data from the database.
I've deleted some of the code here,
but much of it is still the same.
We're still connecting to the database,
we've got the user's timezone,
and now here we're selecting star from posts.
For example, here we have the content and the date,
which is a UTC timestamp.
So what we would do normally is something like
for posting cursor, for example.
And then we can do underscore ID,
content and timestamp equal post to the structure
the post tuple into the three variables.
And now what we have to do is read the timestamp as if
it was a UTC timestamp.
So we can do naive daytime, for example.
Equals date time.from timestamp
and putting the timestamp in there.
If we do this though, oh sorry,
this has to be datetime.from timestamp.
If we do this though, it's going to assume that
the timestamp we're reading is a timestamp in local time,
and not in UTC time.
So it's very important when you're reading UTC timestamps
that you type UTC from timestamp.
Then we can get the UTC date by saying pytz.utc.localize
and pass in the naive daytime
and then we can convert that to local date
using UTC date.astimezone, with a user's timezone.
Finally, we can put into the local date
if you want as well as the content.
Well this is going to do is it's going to grab
the timestamp loaded into a date time object as
if it was UTC but without timezone information.
Then using pytz.utc.localize,
where adding timezone information,
without changing its contents.
And finally, using UTC date as timezone we are converting
that date into the new timezone.
And this takes care of things like daylight savings,
and so on, which are so very difficult to deal with.
All right, so hopefully these three snippets,
the config file, loading and saving into a database do help.
I hope you've learned something in this video,
we're gonna make use of this now in our polling project.
So what I'm gonna do is delete dates.py,
and we can go back to our project in the next video.
I'll see you there.