MySQL 33 - Datetime, Date, Time Data Types

Sharing buttons:

yo yo what's up Caleb back on the

youtubes how you guys do in this video

we are going to be talking about date

date time and time so these are three

data types that MySQL has to store dates

and times yeah I know as you can tell by

SQL likes to really you know separate

things out make a lot of extra data

types for us simple-minded who like

complexity right I'm kidding with joke

anywho there are three data types used

to do basically the same thing so we

have day time date and time and the

difference between these are very

obscure I'm just playing this obviously

okay so let's start with daytime date

time is separated into date and time

which are essentially the same as these

data types so you can store two in one

using date/time now there's something

particularly special about labeling a

column one of these data types and that

is that MySQL can work with that data

especially it knows that it's working

with a date you could in theory not use

these data types for example you can

have a bar chart column within here you

can say something like that and this

works fine that's one way you can store

dates but overall it's just pretty

craptastic you need I need to like

become a little bit more professional

here this is a very unprofessional

approach to do anything of course I'm

sure there are certain situations when

you will want to store a date as just a

sequence of characters in a string but

in general you are going to want to use

a data type like date/time date or time

there's also timestamp which we'll be

getting into that in an upcoming video

so if you have a column that is of one

of these data types how do you give it

the data is it date a number or is it a

string well we just discussed that it

wasn't a string it works a little

differently so how do you do it well you

actually pass it as a string so you

quote it when you give it data now what

goes in this

has to do with conventions so as we've

discussed conventions are just and

agreed upon something that everyone uses

just to make life easier so for example

if I give you something like this and I

say it's a time well you would say yeah

the time is 6:30 and you know what that

means you automatically know that 6:00

well this is how it works in the USA it

works differently elsewhere I apologize

I am uninformed anyways this is the hour

and this is the mist the colon here is

just convention we decided hey let's put

a colon here if we could just put

someone else you know we could have said

6 - 30 or 6-pound 30 is a convention

well MySQL has its own way of

representing dates and times and we need

to become familiar with how MySQL does

that or else we might think something

else for example someone who doesn't

know this convention might look at this

so the bad maple hook-up there's like

this it's the 12th hour of the day and

it's the 6 minute and then it's all

confusing so let's learn how MySQL

represents base and time first let's

discuss dates I will give you the way

MySQL stores them and then I'll show you

some common ways that we as human beings

Express dates so this is an example of a

date that we could pass to MySQL and

it's very clear from this example which

one is the year obviously that one

because it has four numbers but here is

where ambiguity shows up which one of

these is talking about the month and

which one's talking about the day you

can buy this example figure it out

because 22 only makes sense in the

context of it being a day so in summary

this is the year this is the month and

this is the day this is in contrast to

some ways we express dates as humans

some people would represent this this

others would represent it like this some

people would represent it like this so

you can see it's very important to

understand what is what when we are

working with dates now MySQL can be a

little bit lenient in how you give it a

date and we'll be discussing that in a

future video but for now understand

this is the standard format for a date

now let's move on to the time this is an

example of a time where 22 is the hour

54 is a minutes and 30 is the second and

you can see that the delimiter or the

separator is different

we have colons for the time and hyphens

for the date now the hour this way look

interesting to some of you this is in 24

hour time or as some people would say

military time so if you're not used to

that you can take 22 subtract 12 and we

figure out that it's 10 so this will be

the equivalent to 10:54 p.m. now a cool

thing with MySQL is that we can actually

have fractional seconds so we can put a

period right here and add fractions up

to 6 decimals which I believe would be

microseconds but it's not very often I

worked with time that small so in

general I don't really need any of this

data and I don't really worry a whole

lot about it

but this is something that can set my

SQL apart from some other database

management systems now in order to do

this when you specify the data type you

have to give it some kind of precision

in parentheses so for example we would

say time 6 which would allow up to 6

decimal places here what less you could

do something like 3 which would limit

you to just that many now let's say we

have a column that is of a data type 8

time and we'll say precision of 6 how do

we pass data into this so let's go

through a very concrete example of what

we would give it everything is going to

be inside of a quote when we start with

the date now after the date we put a

space and we start with the time and

then after the time we can put a period

for fractional seconds and there you go

that is an example of a date/time value

that is all I have for this video

although I do have more to say about

date time and date time and also some of

the other data types we're going to come

across so please stick with the series

if you've enjoyed it please click like

subscribe and as always I'll see you in

the next video

thanks guys