Whenever you enter a date into a search form, you are reaping the benefit of the databases, having
an awareness of the current time and received from the server, plus the ability to handle formats for
dates, times and nuances of the calendar, such as a leap year and time zones.
So this is essential for storytelling with data because they usually, when something occurred, is
usually a valuable as question, as who, what or how many were involved.
So here we have four types of data and times here.
So I will open this.
So let us here.
So we have data, data and time data types.
So here we have firstly, of course we will need to make some here.
We have a data type here and after that we will have storage size, storage size and after that we will
description.
After that we will add range.
So here firstly we will have timestamp time stamp.
The storage size of timestamp is eight bytes and description is date.
They date.
And.
Um.
Type.
Yeah.
Date and time.
Here and let's make a range here.
So that's it.
And the ranges.
44,713
here and PC And from this here.
2942.
And 7907, six, seven, six.
Add here.
That's it.
And.
We also have date.
Date.
And in date we have storage size of four bytes.
Four bytes.
And here we have only date, but no time.
And the range is.
The range starts Same.
Back to like it has more add than timestamp.
So it's 5874.
800 897.
Add.
And here we also have time, which is time is eight bytes.
Eight bytes.
And the time only stores time and no date.
The theme time starts from 000.
And also it counts seconds to 24 0000 here.
And we also have interval.
Lastly, interval.
This is here.
This is 16 bytes.
And here we have just had time interval interval and it starts plus minus here.
118 000000 181 178 million years.
That's it.
So now the time stamp here.
Time stamp.
Let's actually make it like that.
The time stamp records date and time, which are useful for a range of situations.
You might track departures and arrivals of passenger flights, a schedule of Major League baseball games,
football games or incidents along a timeline.
So you will always, almost always want to add the keywords with time zone to ensure that the time recorded
for an event includes the time zone where it occurred.
Otherwise, times recorded in the various places around the globe become impossible to compare, so
the former timestamp with Time zone is a part of the SQL standard.
With PostgreSQL you can specify the same data type using the time stamps time.
Stamps here.
And we also have date here.
This is records just a date.
And this is a part of the SQL standard.
We have also time here.
The time records just a time.
And this is a part of the SQL standard, although you can add with time zone keywords without a date,
the time zone will be meaningless.
And here we have also.
Uh, interval.
The interval holds a value representing an unit of time expressed in the format quantity unit, and
it doesn't record the start or end of the time period only its length.
So examples include, for example, 12 days or eight hours.
And here you can also find the SQL PostgreSQL documentation lists, unit values ranging from microseconds
to millennium.
So now let's go to writing our code here, right?
So we also had some code that I will share that as an attachment, but it has nothing to do with our
today's lectures.
So we will.
Uh, create a new table.
That's why we're going to go to here.
As you can see, we have tables right click on this create table.
Now, of course, we're going to use the query tool as well.
We will create SQL queries.
So create table and date.
I will explain all of these codes here, date time types, and here we will have timestamp also.
Let's actually.
Yeah.
Timestamp.
Column and timestamp with time zone.
Time zone.
Here.
After that we will have interval.
Column.
Interval.
Column.
Interval.
That's it.
And here we will also add another code here, which is insert.
We will insert into datetime types.
And this we're going to have some values in it.
Values, which is these values include 2000.
23.
The month and then day.
Here.
After that, we will add.
Hours here ist And after that we will add here.
To.
Base.
Note that another value in it.
So here we will add same date, 2103.
January.
Or this number here or this 31.
And here, 0100 minus eight.
And after that, we will make it.
One.
Month.
After that, we will add a comma here.
We will add another 1323 1230 101 here and we will add let's actually add some Australia, right?
So Australia.
Mel.
Or so here.
We will add.
After that, we will do it one.
Century.
And after that.
We will list all of the information that we wrote here, which is using the select asterisk from date
time types.
Here and here we create a table with a column for both types and here and insert we insert a.
Uh, for also, we also need to insert another row here, which is.
It's going to be now.
Now.
You will see what this magic does, what this function does, and it's going to be one week.
So that's it.
So here we first create a column for both types.
Timestamp.
Timestamp with time zone and interval.
And after that.
I insert our four rows.
So for the first three rows are we Our insert for the timestamp column uses the same date and time.
December 31st, 2020.
The chamber.
Yeah, it's the chamber.
December 31st, 2023 and at 1 a.m. and using the International Organization for Standardization ISO
format for the dates and times.
Firstly, we got years, months and days and also we have hours, minutes and seconds.
In this case we didn't add any seconds here, so SQL supports additional date formats such as months,
days, years, but ISO is recommended for portability worldwide.
So following the time we specify a time zone but use a different format in each of the first three results.
And so in the first row we use the abbreviation here, which is a Eastern Standard Time in the United
States.
In the second row, we set the time zone with the value minus eight.
That represents the number of hours difference or offset from the coordinated universal time UTC and
the time standard for the world.
The value UTC is plus -zero hours and zero minutes, so minus eight specifies a time zone, eight hours
behind UTC.
So in in the United States, when daylight daylight saving time is in effect, minus eight is the value
for the Alaska time zone.
So from November through the early March, when the United States reverts to a standard time, it refers
to the Pacific Time, time zone.
So for the third row here, we specify the time zone using the name of an area and location, which
is Australia.
Melbourne.
So that format uses values found in standard time zone database, often employed in computer programming.
You can learn this more from here in Wikipedia TZ database and here you will see.
Time zones, as you can see here, America.
Asia.
Macassar Manila.
Muscat.
Nicosia.
And so on.
And here we also have the.
UTC offset State DST and time zone abbreviation.
That's it.
And let's run the script now.
Location data type does not exist.
So.
Because.
Yes.
Create data date out, not data date time.
That's it.
As you can see here, this is our output.
Even though we supply the same date and time in the first three rows on the timestamp column.
As you can see here, the rows output differentiates, right?
So the reason is that Pgadmin reports the date and time relative to my time zone, which is the result
shown is indicated by the UTC offset of minus five you see here, minus five is T minus one.
And Australia, Melbourne.
So at the end of the each time step of course.
So a use UTC offset of -0 five means five hours behind UTC equivalent to the US Eastern time zone during
fall and winter months when standard time is observed.
So if you live in different time zone you will likely to see a different offset.
And the times and dates also may differ from what's shown here.
So we can change how PostgreSQL reports the timestamp values and I will cover how to do that, plus
other tips for wrangling dates and times in the next lectures.
And finally, the interval column shows the value you entered and PostgreSQL changed one century to
100 years here.
And one week to seven days because of its preferred default setting for interval the display.
And you can also read the interval input section of the PostgreSQL documentation on their official website
to learn more about these interval inputs here.
So I'm waiting you in next lecture.