Detailed Notes
1. Date and time types DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS. YEAR - format YYYY or YY. 2. Date comparisons 3. Date arithmetic
create table all_dates( type_date DATE, type_datetime DATETIME, type_timestamp TIMESTAMP, type_year YEAR ); insert into all_dates(type_date,type_datetime,type_timestamp,type_year) values (now(),now(),now(),now());
insert into all_dates(type_date,type_datetime,type_timestamp,type_year) values ('2000-01-01 01:00:00','2000-01-01 01:00:00','2000-01-01 01:00:00','2000'); insert into all_dates(type_date,type_datetime,type_year) values ('2000-01-01 01:00:00','2000-01-01 01:00:00','2000'); update all_dates set type_year = '2020' where type_year = '2000'
create table all_dates2( `date` DATE, `date time` DATETIME, `time stamp` TIMESTAMP, `year created` YEAR );
Transcript Text
[Music] well hello and welcome back we are continuing looking at sql uh particularly mysql and mariadb and doing some uh you know basically some tutorials as we're working our way through this uh this episode we are going to look at some of the date related types data types and so in order to do this let's go ahead and start by creating a table and with this table probably easier to do it this way i create this table uh oops and i should probably get rid of that comma there we go and let's look at that i'm going to create a table called all dates and i've got four types here i don't have a primary key or an id or anything like that this is just for mess around and you'll see here i have a date a date time a time stamp in a year now the interesting thing about and each these are the four date types in sql the interesting thing you probably see right away is that i've got a default current time stamp and on update current time stamp for this type timestamp and it can't be null so that while a uh well i'll take a look i'll show you what that data looks like in just a second so let's start with that so let's go ahead and just insert a record where we're just going to use now which is basically a way to say what is the time so if i do just do this if i do select now from [Music] all dates it's just going to show me up and it's not going to show me that darn it oh because i don't have that so let's do that for my addresses there we go so it's just going to do now which is this is the date and time according to the system that we're on to that server date and time so now if i go in here and i'm just going to insert a record that's now for each of those types and we're going to see here that it's not too happy about it oh because uh i didn't do that right let's do this uh and this is going to be a type date whoops hey when i struggle to type okay so type date type date time type timestamp and type year okay so now we're gonna take that let's just copy and paste that over here and now type year tells me it's an in-price it's an incorrect date time so what i'd have to do here because type year is just a year it's not a date time i can do this i can take just the year part of now and now it's going to work and we'll look at that in just a minute so now if i do select star from uh oh dates so i can see that i have my type date my first one it's just a date it gives me a year and this is uh set to your local standards and looks it may look a little different on yours depending on what your environmental stuff is but with the date note i have just the date it's a month 04 day 20 and then the year and it's actually it's why i want you know it's this format four digit year two digit month two digit day for a date time i get the date a space and then i get the 24 hour time so this is this says it's one in the afternoon 1pm 24 minutes after 44 seconds after timestamp is going to display in this case as a date time as well sometimes with the timestamp you will see an integer value and that is it depends on what it is it's basically it's a system time and it is a way that this database can store a time as a big integer as opposed to this this date format and of course with the year we see that the type year is 2022. now with this one uh let's see if i do select uh let me do now from all dates let's play around with that for a little bit now with that there are some things i can do so i can do year and it's going to give me the year i can do i believe i can do time let's try that and it gives me just the time and these are all ways to quickly pull do some quick formatting if i need a date gives me just a date i can do i think i do month yep so it gives me the month and this is who this is the integer month months start january is is month one just to be sure uh up to december which is month 12. i can do uh i can even do down like i can do hour and it's going to give me the 24 the military time hour now if i do the same insert and let's uh let's change this around let's do this let's take this same insert but now instead of using the now i'm going to change this up and i'm going to use so let's say let's stay say the year 2000 and the time stamp will be uh 2 000 0101 one zero zero zero zero so let's do that and let's do the same thing whoops over did it let's do the same thing so here now note before when i did now it made some adjustments let's see what it does if it makes those same adjustments when i do this as a string okay i get the same thing i do or i get it no error so if i just like start from all dates now we see that i got the same thing it it went ahead and again on the type date it just truncated off the time so i'm still good there but notice while that defaults uh somewhere up here for timestamp is the current timestamp i'm able to overwrite it because that's as we've seen before that is the default but if i take that same entry and now i get rid of the timestamp and we'll see where this becomes very useful here okay so i'm going to take that guy and now i can see that he gives me a timestamp he gives me when this was created now note note that uh 1329 now let me do this let me do the same thing and let's do update all dates set year equal uh let's do 2020 where year equals 2 000. so let's take this and do an update oh sorry it's not year it is type here helps remember the column name probably wouldn't like here anyways there we go oh okay so now if we do select star from all dates now notice here that um so we just did an update on these two fields i went from 2000 to 2020. and notice before our update our timestamp was 2000 it's 2022 at 13 29 21 well now that time stamp because we didn't include it it is an update timestamp so we have a last modified that we can easily do within the table now there are some other things we can do with the timestamp we could do it on created we can set some information there we can change around some of the defaults particularly because if you see here the default is current timestamp but we'll see here it has this thing that we're going to talk about later called a trigger that says on update current timestamp so when i do an update if i don't have this in there it's going to give that current timestamp now you notice that the other dates stayed the same that 2000 you know 1 1 2 000 that stuff i'll stay the same but the time stamp updated itself and of course a year did because i told it to now while we are talking about this i do want to briefly mention if i've tried to create a table oh let's call it all dates to let me use some reserve words and let me show you what happens here this could get very confusing to the database but so if i create this one i'm going to get errors because these are reserved words now what i can do is i can escape them essentially i can do these tick marks just say look quote this thing out and that means though that every time oh yeah it's not going to like that at all oh my mistake let's do this okay and let me get rid of that so i don't keep blowing that up on you guys so let's do this here first whoops okay give her the comma let's see what happens okay so i do get to do it but i think i'm going to get insert into let's see if this works because i'm using reserved words so if i do date comma date time comma time stamp comma year values now oh whoop come here down down now oh let's make that a year let's see if works oh it does so even though it could get pretty confusing when you start doing queries you can use those words as column names sometimes you'll run into issues with it though and in that case you will want to use tick marks also you'll see this a lot if you are coming from a system where you have spaces in the name so if this was like date time time stamp you're created you know something like that you'll sometimes see those you'll have to use those tick marks in order to tell it that this is actually a string i want to use instead of a reserved word now we can do let's go back to select star from all dates from all dates there we go now let's do that's a little arithmetic so i'm going to do select star from all dates where let's see type date less than 20220101 and i'm going to get that i can see that i can use greater than and less than with my dates i can probably even do i haven't tried this before but we're going to try this anyways i think i can do a like with this so i can say where it's like 2000 and yep sure enough it's going to give me that so and actually i think we have stumbled into that before is it can treat it almost like a string when you use a like so we can use our less than we can use our greater than things like that we can even do an equal uh we're type date equals 2011. and i can even do not equal and that's going to give me the 2022 date so i can go in and and do that fairly easily it's not going to it's not rocket science it's not something that we're gonna sit there and go oh wow this is you know this is new and unusual because it's basically the same thing we've seen with a lot of other with numbers and even strings things of that nature now the next thing we we want to play with briefly is date arithmetic which is where we are going to add something so if i want to do select so when i do select now from uh all dates let's do from all dates too now that's going to show me this time now if i do a plus one um i can do it like that now i'm going to see it as a as this integer what i really want to do is i want to do select let's do this so i can do select year of now plus one and so now i'm getting that year i can see that i've got from 2022 to 2023 if i do dates let's see what the date gives me and now i see it's 4 21 instead if i did the four i'm sorry if i did that that would be the 2022 but note that when i go with the the plus it's actually converting it into this integer thing and actually let's do it this way let's do uh now plus one comma now so you can see the two may be easier to see so we see here this is at 13 38 21 and we can see it adds to the last part here so i can go um i can add them you know i can add a minute i could i'm sorry that's a second in that case because it's adding to that but i could also do uh let's see that well yeah so let's start with that noting that it gives me this slightly different format when i do it when i'm coming out of it uh but i can do an update let's see if i do update well let's do this so we can see it all dates and then if i do update all dates set uh let's say type date type whoops type date plus one oops well let's do it on the dates so it's that one and now do it we can see that here instead of oh let's take this over here that's probably easier to read here sorry if we look here we can see that it went from 420 and one one to 421 it so it's on the type date time we get the last at the the uh what is that hour's minute second on a date we get it at the day and so it's just taken as adding one to that less number in this case we're doing at one now we can do more complicated date arithmetic and that's like our very basics if we want to get a little more complicated then there's some additional functions that we will come back and visit like i said there's a lot about dates that is worth our review time but i think that's it for today that wraps up our time so i'll let you get back to it we'll come back and we're just going to keep digging our way through these things as you've seen we've talked about things like stored procedures we've or mentioned and triggers and some things like that that we will start getting into along the way that being said go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing looking at sql uh
particularly mysql and mariadb and doing
some
uh you know basically some tutorials as
we're working our way through this
uh this episode we are going to look at
some of the date related types data
types
and so
in order to do this let's go ahead and
start by
creating a table and with this table
probably easier to do it this way i
create this table
uh oops
and i should probably get rid of that
comma there we go
and let's look at that i'm going to
create a table called all dates
and i've got four
types here i don't have a primary key
or an id or anything like that this is
just for mess around
and you'll see here
i have a date
a date time a time stamp in a year
now the interesting thing about and each
these are the four date types in sql the
interesting thing you probably see right
away
is that i've got a default current time
stamp and on update current time stamp
for this type timestamp
and it can't be null
so
that
while a
uh well i'll take a look i'll show you
what that data looks like in just a
second so let's start with that
so let's go ahead and just insert a
record
where
we're just going to use
now which is basically a way to say what
is the time so if i do
just do this if i do select now
from
[Music]
all dates
it's just going to show me
up and it's not going to show me that
darn it
oh because i don't have that so let's do
that for my addresses
there we go
so it's just going to do now which is
this is the date and time according to
the system that we're on to that server
date and time so now if i go in here and
i'm just going to insert a record that's
now
for each of those types
and we're going to see here
that it's not too happy about it oh
because
uh
i didn't do that right
let's do this uh
and this is going to be
a
type date whoops
hey when i struggle to type
okay so type date
type date time
type timestamp
and type year
okay so now we're gonna take that let's
just copy and paste that over here
and now
type year
tells me it's an in-price it's an
incorrect date time so what i'd have to
do here because type year is
just a year it's not a date time
i can do this i can take just the year
part of now
and now it's going to work
and we'll look at that in just a minute
so now if i do select star from
uh
oh
dates
so i can see that i have my
type date my first one it's just a date
it gives me a year and this is uh set to
your local
standards and looks it may look a little
different on yours depending on what
your environmental stuff is
but with the date note i have just the
date it's a month 04
day 20 and then the year and it's
actually it's why i want you know it's
this format
four digit year two digit month two
digit day
for a date time
i get the date
a space and then i get the 24 hour time
so this is this says it's one in the
afternoon 1pm
24 minutes after 44 seconds after
timestamp is going to display
in this case as a date time as well
sometimes with the timestamp you will
see an integer value and that is
it depends on what it is it's basically
it's a system time
and it is a way that this database can
store a time as a big integer
as opposed to this
this date format
and of course with the year
we see that the type year is
2022.
now with this one
uh let's see if i do select uh let me do
now from
all dates
let's play around with that for a little
bit
now
with that
there are some things i can do so i can
do year
and it's going to give me the year
i can do
i believe i can do time let's try that
and it gives me just the time
and these are all ways to quickly pull
do some quick formatting if i need a
date gives me just a date
i can do
i think i do month
yep so it gives me the month and this is
who
this is the
integer month months start january is is
month one
just to be sure uh up to december which
is month 12.
i can do
uh i can even do down like i can do hour
and it's going to give me the 24 the
military time hour
now if i do the same insert
and let's
uh let's change this around
let's do this
let's take this same insert
but now
instead of using the now i'm going to
change this up and i'm going to use
so let's say let's stay say the year
2000
and the time stamp will be uh 2 000
0101 one zero zero zero zero
so let's do that
and let's do the same thing whoops
over did it
let's do the same thing
so here
now note before when i did now
it made some adjustments let's see what
it does if it makes those same
adjustments when i do this as a string
okay i get the same thing i do or i get
it no error so if i just like start from
all dates
now we see
that i got the same thing it it went
ahead and again on the type date it just
truncated off the time
so i'm still good there
but notice
while that defaults
uh somewhere up here for timestamp is
the current timestamp
i'm able to overwrite it because that's
as we've seen before that is the default
but
if i take that same entry
and now
i get rid of the timestamp
and we'll see where this becomes very
useful
here
okay so i'm going to take that guy
and now i can see that he gives me a
timestamp
he gives me when this was created now
note note that uh 1329 now let me do
this let me do the same thing and let's
do update
all dates
set
year equal uh let's do 2020
where year
equals
2 000.
so let's take this and do an update
oh sorry it's not year it is type here
helps remember the column name
probably wouldn't like here anyways
there we go
oh
okay so now if we do select star from
all dates
now notice here
that um
so we just did an update on these two
fields i went from 2000 to 2020. and
notice before our update our timestamp
was 2000 it's 2022 at 13 29 21 well now
that time stamp because we didn't
include it
it is an update timestamp
so we have a last modified that we can
easily do
within the table now there are some
other things we can do with the
timestamp we could do it on created we
can set some information there we can
change around some of the defaults
particularly because if you see here
the default is current timestamp but
we'll see here it has this thing that
we're going to talk about later called a
trigger
that says on update
current timestamp so when i do an update
if i don't have this in there it's going
to give that current timestamp
now you notice that the other dates
stayed the same
that 2000 you know 1 1 2 000 that stuff
i'll stay the same but the time stamp
updated itself and of course a year did
because i told it to
now while we are
talking about this i do want to briefly
mention
if i've tried to create a table
oh let's call it all dates to
let me use some reserve words and let me
show you what happens here
this could get very confusing to the
database but so if i create this one
i'm going to get errors because these
are reserved words now what i can do
is i can escape them
essentially i can do these tick marks
just say look
quote this thing out and that means
though that
every time
oh yeah it's not going to like that at
all
oh my mistake
let's do this
okay and let me get rid of that so i
don't keep blowing that up on you guys
so let's do this here first whoops okay
give her the comma
let's see what
happens okay so i do get to do it
but
i think i'm going to get
insert
into let's see if this works
because i'm using reserved words so if i
do date comma date time
comma
time stamp
comma
year
values now
oh
whoop come here
down down now
oh
let's make that a year
let's see if works oh it does
so even though it could get pretty
confusing when you start doing queries
you can use those words as
column names
sometimes you'll run into issues with it
though and in that case you will want to
use
tick marks
also
you'll see this a lot if you are coming
from
a system where you have spaces
in the name so if this was like date
time time stamp
you're created you know something like
that you'll sometimes see those you'll
have to use those tick marks
in order to tell it that this is
actually a string i want to use instead
of a reserved word
now
we can do let's go back to select star
from all dates
from all dates there we go now let's do
that's a little arithmetic so i'm going
to do select star from
all dates
where
let's see
type
date
less than 20220101
and i'm going to get that i can see that
i can use greater than and less than
with my dates
i can probably even do
i haven't tried this before but we're
going to try this anyways i think i can
do a like with this so i can say where
it's like 2000
and yep sure enough it's going to give
me that so
and actually i think we have stumbled
into that before is it can treat it
almost like a string when you use a like
so we can use our less than we can use
our greater than
things like that we can even do an equal
uh we're type date
equals 2011.
and i can even do not equal
and that's going to give me the 2022
date
so i can go in and and do that fairly
easily
it's not going to it's not rocket
science it's not something that we're
gonna sit there and go oh wow this is
you know this is new and unusual because
it's basically the same thing we've seen
with a lot of other with numbers and
even strings things of that nature
now
the next thing we we want to play with
briefly is date arithmetic which is
where we are going to add something so
if i want to do select
so when i do select now from
uh all dates
let's do from all dates too
now
that's going to show me this time now if
i do a plus one
um i can do it like that
now i'm going to see it as
a
as this integer what i really want to do
is i want to do
select
let's do this so i can do select year
of now plus one
and so now i'm getting that year
i can see that i've got from 2022 to
2023 if i do dates let's see what the
date gives me
and now i see it's 4 21 instead if i did
the four
i'm sorry if i did that
that would be the 2022 but note that
when i go with the the plus
it's actually
converting it into this integer thing
and actually let's do it this way let's
do uh
now plus one comma now so you can see
the two
may be easier to see
so we see here this is at 13 38 21
and we can see it adds to the last part
here
so i can go
um
i can add them you know i can add a
minute i could i'm sorry that's a second
in that case because it's adding to that
but i could also do
uh let's see
that well yeah so let's start with that
noting that it gives me this slightly
different format when i do it when i'm
coming out of it
uh but i can do an update let's see if i
do update well let's do this
so we can see it
all dates
and then if i do
update
all dates
set
uh let's say type
date
type
whoops
type date
plus one
oops well let's do it on the dates so
it's that one
and now do it
we can see that here
instead of oh let's take this over here
that's probably easier to read here
sorry
if we look here we can see that it went
from 420
and one one to 421 it so it's on the
type date time we get the last at the
the uh what is that hour's minute second
on a date we get it at the day
and so it's just taken as adding one to
that less number in this case we're
doing at one
now we can do more complicated date
arithmetic and
that's like our very basics if we want
to get a little more complicated then
there's some additional functions that
we will come back and visit like i said
there's a lot about dates that
is worth our review time but i think
that's it for today that wraps up our
time so
i'll let you get back to it we'll come
back and we're just going to keep
digging our way through these things as
you've seen we've talked about things
like stored procedures we've or
mentioned and triggers and some things
like that that we will start getting
into along the way
that being said
go out there and have yourself a great
day a great week and we will talk to you
next time
you