Detailed Notes
1. Date and time functions select adddate(type_datetime, INTERVAL 1 DAY), adddate(type_date, INTERVAL 1 DAY), adddate(type_year, INTERVAL 1 DAY) from all_dates; select adddate(type_datetime, INTERVAL 1 MONTH), adddate(type_date, INTERVAL 1 MONTH), adddate(type_year, INTERVAL 1 MONTH) from all_dates;
select adddate(type_datetime, INTERVAL -2 MONTH), adddate(type_date, INTERVAL -2 MONTH), adddate(type_year, INTERVAL -2 MONTH) from all_dates;
select adddate(type_datetime, INTERVAL 1 YEAR), adddate(type_date, INTERVAL 1 YEAR), adddate(type_year, INTERVAL 1 YEAR) from all_dates;
select year(adddate(type_datetime, INTERVAL 1 YEAR)) dtm, year(adddate(type_date, INTERVAL 1 YEAR)) dt, year(adddate(type_year, INTERVAL 1 YEAR)) yr from all_dates;
select dayname(type_datetime), dayname(type_date) from all_dates;
select adddate(type_datetime, INTERVAL 5 SECOND), adddate(type_date, INTERVAL 5 SECOND) from all_dates;
select datediff(type_datetime,now(quit)), type_datetime, now() from all_dates; select datediff(type_datetime,now()) / 365, type_datetime, now() from all_dates;
Transcript Text
[Music] hello and welcome back we are continuing our sql uh tutorial and examples and we're moving right along focusing on of course my sequel and maria db and today we're going to continue sort of from the last episode where we talked about dates and times and date times and time stamps and we're just going to touch on some of the functions that are built in to my sql so if we just start from what we call it all dates i'm going to show these real quick so i should probably say from okay so we've got our little date table oh and to finish that thought we're going to touch on this because there are dozens of functions around date stuff you can google them if you google like my sql date functions you'll see should be able to see some pretty good examples of them and you can see the documentation and see what all is in there but i do want to give you a a feel for what is there so you're not you know banging your head against the wall trying to get things trying to do something that's already been done for you so the first one i want to do is let's do this here select star from all dates except for let's do uh we're gonna do the date time first well actually let's do this what we're going to do is date time and we're also going to use typed actually we're going to use all of these let's do this because it may be easiest to see what happens with each of these so first one let's start with um the arithmetic we talked about that last time and so there's a there's a thing called add date or add time which is no underscore and it's just going to be add date like that and let's put him here and i want to show you what happens in each of these cases and the format for this the format for this is that we have an interval and let's do let's start with just one day and you're going to see that we can actually this interval can be a whole lot of interesting things so first let's do this and see what it tells us so we're adding one day to each of these now adding a date to a year is just going to return back a a null adding a day to a regular date we were this is the type date so 421 one two one two now it's 422 one three one three and the date time was four twenty one one and we'd see four twenty one one two now we can do the same thing and we can change it so let's instead of a day that's at a month so let's do that we still get anything on our year but we now see our months of being incremented we can do the same thing let's do it this way it's pretty easier this way and let's go ahead and subtract two months uh there we go so it went from like five to ah yeah i must have missed that somewhere i added an extra one somewhere okay um but now notice that like here where we subtract two we went from uh what is it here let me do that one more time oh that's right i'm sorry i was thinking it was updating but this is i'm actually doing the select i'm not actually doing an update i confused myself so we started with let's do that so we started at 4 20 uh and the type date time uh type date i'm sorry so we had four so it's basically april january january when we subtract two notice that we jump back to february but here it even does our arithmetic so it says that hey we went to a prior year and uh it does that with these as well again type year hasn't been touched now let's do but oh i'm sorry and that was on the subtract so there's our subtract now so now let's take we've done a day let's add a year and so now we still don't see in the year because it's not it doesn't really treat it i guess properly as a date type in a sense uh what we do see is uh here so we've got our we've incremented remember these were 2 000. um so we increment our years let's go back up here so we can see them uh date time was 22 2000 2000 and date time is now 23 2001 2001 same thing on our type date year note we're still not getting it here is effectively a um more like a number although it's a special you know in a sense it's still it's basically a number so you're not going to see that as useful as some of the other dates which brings us to uh which we've already seen what i can do is i can take the same thing and i can actually cast stuff so if i take just the year out of each of these and we're going to see the same thing i could take a year i could take i could do a year i could do month i could date i think we saw like we can do before even that we can do hour we can do minute and even second so now let's just take that and see what happens well i need to paste that and now the year of uh oh this is sorry it got too big there because of that um let me cast this so let's just do uh that's going to be dt that's going to be just oh that's going to be ttm dt and year let's do this way this is a nice way also so we can shrink that thing now we've got a nice small thing okay and we can see that for the date time we got just a year date so we don't really you know years they have their uses but uh and particularly because they're smaller but typically you may just you're probably better off using a daytime or you know a date uh let's see so time let's see what else we want to look at now we can also do a date sub so we can we can do and actually there's a there's date there's date add there's also add date i'm sorry date add and he's going to run the same way um so there's a couple of of aliases basically of methods or i guess functions i should say now you can do uh let's see let's go back up to let's just start this one we can also do things like this we could do a day of year let's do this which you can also do day of week day of month um while i type so i can do day of year for each of these which is a nice way to quickly grab like particularly with the year i mean i guess week awesome also happens quite a bit where you're like hey i want to know if it's the and we'll show that in a second if it's a monday sunday tuesday whatever it is day of the week so day of year i'm just going to take type year out because it's not going to like that um so we'll show you that yeah see it's still null but here we can see the day of the year um so april what was that april 21st is obviously or apparently the 110th day of the year and this depends on um leap day or i'm sorry leap years so depending on the year you may have a slightly different day of the year obviously you can also do which is fairly useful for us on a regular basis i want to show you that one i can do day of week and i can see which day of the week each of these are and that's not always useful to see you know which day the week is at and what i can do is i can do obtain it if i can type it right day name so i can take that and now it doesn't like that let's do oh probably because it's it doesn't do it off of that it has to do it off of this so instead of day of week i can do day name these are easy to forget there we go so now we see wednesday saturdays sunday and thursday these are easy to forget because there's a lot of a lot of little variations of this and if you're in other databases oracle sql server db2 whatever they're going to be most likely very similar type functions they may work a little differently and so those are one things you're just gonna have to worry about is you're gonna have to figure out what is it that i'm i need to use in this database what's the format you know which parameter comes in which order uh now the interval thing is a little different a lot of times it's gonna be like a you know plus one minus one or stuff like that and then it may say you know a type or something like that so that is where it's gonna be different uh you're not gonna see stuff that looks like this uh you know add date interval one day kind of stuff and that does work for year month day hour minute second so i can do i can do this same thing and i can add a second i can add five seconds let's do this and i think i can even put an s at the end i'm gonna find out in a second no pun intended so let's see let's do it this way right first so i got that now let's see i think it will allow me to put an s there oh no it doesn't okay i was thinking it might but it doesn't so it's always going to be singular but the interval the number and then the type and so i could do second i can do minute i can do hour so we do a lot of that fairly straightforward fairly quickly we also saw our casting so we can cast a year to month today um oh that's what i want to do is let's do uh let's see i don't want to do this okay i'm going to do oops let's do this i'm going to take these and now i'm going to use if we want to compare some we can always do less than greater than um equals 2 not equal to which we've already seen but i can also do i can compare two dates so let me do that too now and then to now and here's the difference and it's going to be in seconds um so and we can see that uh now to uh and it's let's see how to do it it's basically the first one minus the second one is how you do it so if i flip those what's the difference between so to go from if i want to know you know if i want to go from type date time that value to now i have to add that back in and so this is this this first part is uh 800 8146 seconds less than this and if i run it again because it's now we're going to see oh no it is about the same thing so interesting oh that may be in minutes my mistake let's look at it this way first ah now we'll leave it like that and i want to take that let's say times 60. oh i'm sorry divided by 60. uh let's do divided by 3 600. uh well let's see how many minutes let's do it that way so the difference here is 135 minutes um let's look at it this way this is going to be easier i also want to do um let's do it this way it's going to be easier to see type date time comma now let's do this that way we can actually look at what the value are so if we look at the difference uh over 60. so from uh and this is in the date diff this is important is that for date diff only the date is used and this is the number of days this is a the number of days uh returned so we're going to see here that 135 days is difference between these two uh since this is a partial it's basically oh because i did the i'm sorry that's in days um oh wait no we can't see that because if i do it's not in days that's day so let's do divided by 365. let's do that sorry guys um i'm getting my getting confused on these okay so here we go uh it's so the difference in these and it does take this into account is that or i'm sorry it doesn't take this into account so this is going to be one regardless which i'll show you in a minute but this is basically 22 years difference uh let me go show you that so the date difference even though the times are pretty close um here it's just set 421 to 421 even though you know those are about there so even if i were to change that time i'm still going to get this is the difference in days now there are others so if you want to do time diff uh if you want to do a timestamp comparison you can get those as well so there's quite a bit of did i get that in there yep i got that let's do this and let's do it by that just so we've got it so that's how many years and i think that's a good place to stop for now so we're going to go ahead and wrap this one up and we are going to dive into whatever our next topic happens to be um we've got plenty we'll just see where it takes us but until then go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
hello and welcome back we are continuing
our
sql uh tutorial and examples
and we're moving right along focusing on
of course my sequel and maria db
and today we're going to continue
sort of from the last episode where we
talked about dates and times and date
times and time stamps
and we're just going to touch on
some of the functions that are built in
to
my sql so if we just start from what we
call it all dates i'm going to show
these real quick
so
i should probably say from
okay so we've got our little date table
oh and to finish that thought we're
going to touch on this because there are
dozens
of
functions around date stuff
you can google them if you google like
my sql date functions you'll see should
be able to see some pretty good examples
of them
and you can see the documentation and
see what all is in there
but i do want to give you a
a feel for
what is there
so you're not you know banging your head
against the wall trying to get things
trying to do something that's already
been done for you
so the first one i want to do is let's
do this here select star from
all dates except for let's do
uh we're gonna do the date time first
well actually let's do this what we're
going to do is date time
and we're also going to use typed
actually we're going to use all of these
let's do this
because it may be easiest to see what
happens with each of these so first one
let's start with um
the arithmetic
we talked about that last time and so
there's a
there's a thing called add date or add
time
which is no underscore and it's just
going to be add date
like that
and let's put him here
and i want to show you what happens in
each of these cases
and the format for this
the format for this
is that we have an interval
and let's do
let's start with just one day
and you're going to see that we can
actually this interval can be a whole
lot of interesting things
so first let's do this and see what it
tells us
so we're adding one day to each of these
now adding a date to a year is just
going to return back
a
a null
adding a day to a regular date we were
this is the type date so 421 one two one
two now it's 422 one three one three
and the date time was four twenty one
one and we'd see four twenty one one two
now
we can do the same thing and we can
change it so let's instead of a day
that's at a month
so let's do that
we still get anything on our year
but we now see our months of being
incremented we can do the same thing
let's do it this way it's pretty easier
this way and let's go ahead and subtract
two months
uh there we go
so
it went from like five to
ah
yeah i must have missed that
somewhere i added an extra one somewhere
okay
um
but now notice that like here where we
subtract two we went from
uh what is it here let me do that one
more time
oh that's right i'm sorry i was thinking
it was updating but this is i'm actually
doing the select i'm not actually doing
an update i confused myself
so we started with
let's do that
so we started at 4
20
uh and the type date time
uh type date i'm sorry so we had four
so it's basically april january january
when we subtract two notice that we
jump back to february but here it even
does our arithmetic so it says that hey
we went to a prior year
and uh it does that with these as well
again type year hasn't been touched now
let's do but
oh i'm sorry
and that was on the
subtract so there's our subtract now
so now let's take
we've done a day
let's add a year
and so now we still don't see in the
year because it's not it doesn't really
treat it i guess properly as a date type
in a sense
uh what we do see is
uh here so we've got our
we've incremented remember these were 2
000.
um so we increment our years let's go
back up here so we can see them
uh
date time was 22 2000 2000
and date time is now 23 2001 2001
same thing on our type date year
note we're still not getting it here is
effectively
a
um
more like a number although it's a
special you know in a sense
it's still it's basically a number so
you're not going to see that
as useful as some of the other dates
which brings us to uh which we've
already seen
what i can do
is i can take the same thing
and i can actually cast stuff
so if i take just the year
out of each of these and we're going to
see the same thing i could take a year i
could take i could do a year i could do
month i could date i think we saw like
we can do before even that we can do
hour we can do
minute
and even second
so now let's just take that and see what
happens
well
i need to paste that
and now the year
of
uh oh this is
sorry it got too big there because of
that
um let me cast this so let's just do
uh
that's going to be dt
that's going to be just oh
that's going to be ttm dt
and year
let's do this way this is a nice way
also so we can shrink that thing
now we've got a nice small thing
okay
and we can see that for the date time
we got just a year date so we don't
really you know years they have their
uses but
uh and particularly because they're
smaller
but typically you may just you're
probably better off using a daytime or
you know a date
uh let's see so time
let's see what else we want to look at
now we can also do a date sub so we can
we can do
and actually there's a there's date
there's date add there's also
add date
i'm sorry
date add
and he's going to run the same way
um
so there's a couple of
of aliases basically of methods
or i guess functions i should say
now you can do
uh let's see let's go back up to
let's just start this one
we can also do
things like this we could do a day
of
year let's do this which you can also do
day of week day of month um
while i type
so i can do day of year for each of
these
which is a nice way to
quickly grab like particularly with the
year i mean i guess week awesome also
happens quite a bit where you're like
hey i want to know if it's the and we'll
show that in a second if it's a monday
sunday tuesday
whatever it is day of the week
so day of year i'm just going to take
type year out because it's not going to
like that
um so we'll show you that yeah see it's
still null but here we can see the day
of the year
um so april what was that april 21st is
obviously or apparently the 110th day of
the year and this depends on um leap day
or i'm sorry leap years
so depending on the year
you may have a slightly different
day of the year obviously
you can also do which is fairly useful
for us on a regular basis i want to show
you that one
i can do day of week
and i can see which day of the week each
of these are
and that's not always useful to see you
know which day the week is at and what i
can do is i can do
obtain it
if i can type it right day name
so i can take that
and now it doesn't like that
let's do oh probably because it's it
doesn't do it off of that it has to do
it off of this so instead of day of week
i can do day name
these are easy to forget there we go so
now we see wednesday saturdays sunday
and thursday
these are easy to forget because there's
a lot of
a lot of little variations of this and
if you're in other databases
oracle sql server
db2 whatever
they're going to be most likely very
similar type functions
they may work a little differently and
so
those are one things you're just gonna
have to worry about is you're gonna have
to figure out what is it that
i'm
i need to use in this database what's
the format you know which parameter
comes in which order
uh now the interval thing is a little
different a lot of times it's gonna be
like a you know plus one minus one or
stuff like that and then it may say you
know a type or something like that so
that is where it's gonna be different uh
you're not gonna see stuff that looks
like this uh you know add date interval
one
day kind of stuff
and that does work for year month day
hour minute second so i can do
i can do this same thing and i can add a
second i can add five seconds let's do
this
and i think
i can even put an s at the end i'm gonna
find out in a second
no pun intended
so
let's see let's do it this way right
first so i got that now let's see i
think it will allow me to put an s there
oh no it doesn't okay i was thinking it
might but it doesn't so
it's always going to be singular but the
interval
the number and then the type and so i
could do second i can do minute i can do
hour
so we do a lot of that
fairly straightforward fairly quickly we
also saw our casting so we can cast a
year to month today
um oh that's what i want to do is
let's do
uh
let's see
i don't want to do this okay
i'm going to do oops let's do this i'm
going to take these
and now i'm going to use
if we want to compare some we can always
do less than greater than
um
equals 2 not equal to which we've
already seen
but i can also do
i can compare two dates so let me do
that too
now and then to now
and here's the difference and it's going
to be in
seconds
um
so and we can see that
uh
now
to
uh
and it's let's see how to do it it's
basically the first one minus the second
one
is how you do it so if i flip those
what's the difference between
so to go from
if i want to know you know if i want to
go from type date time that value to now
i have to add that back in
and so this is
this this first part is uh 800 8146
seconds less than this and if i run it
again because it's now we're going to
see oh no it is about the same thing
so interesting
oh that may be in minutes my mistake
let's look at it this way
first
ah now we'll leave it like that and i
want to take that let's say
times 60.
oh
i'm sorry divided by 60.
uh
let's do divided by 3 600. uh well let's
see how many minutes let's do it that
way
so the difference here is
135 minutes
um let's look at it this way this is
going to be easier
i also want to do
um
let's do it this way
it's going to be easier to see
type
date time comma now
let's do this that way we can actually
look at what the value are so if we look
at the difference
uh over 60.
so from
uh and this is in the date diff
this is important is that for date diff
only
the date is used and this is the number
of days this is a the number of days uh
returned so we're going to see here
that 135 days is difference between
these two
uh
since this is a partial it's basically
oh because i did the i'm sorry
that's in days um
oh wait no we can't see that because if
i do it's not in days
that's day so let's do divided by
365.
let's do that
sorry guys
um i'm getting my getting confused on
these okay so here we go
uh it's
so the difference in these and it does
take this into account is that or i'm
sorry it doesn't take this into account
so this is going to be one regardless
which i'll show you in a minute but this
is basically 22 years
difference uh let me go show you that so
the date difference
even though the times are pretty close
um here it's just set 421 to 421 even
though you know those are about there so
even if i were to change that time
i'm still going to get this is the
difference in days
now there are others so if you want to
do time diff
uh if you want to do a timestamp
comparison
you can get those as well
so there's quite a bit of did i get that
in there
yep i got that
let's do this and let's do it by that
just so we've got it
so that's how many years
and
i think that's a good place to stop for
now so we're going to go ahead and wrap
this one up
and we are going to dive into whatever
our next topic happens to be um we've
got plenty we'll just see where it takes
us
but until then go out there and have
yourself a great day a great week and we
will talk to you next time
you