WEBVTT

00:00:01.606 --> 00:00:03.626 align:middle line:84%
You put the datetime
delimiter in front of it.

00:00:03.626 --> 00:00:05.126 align:middle line:84%
If you don't do
that, it won't work.

00:00:05.126 --> 00:00:07.084 align:middle line:84%
If you put just date,
you'll get just the date.

00:00:07.084 --> 00:00:08.716 align:middle line:84%
If you put just time,
you get the time.

00:00:08.716 --> 00:00:17.666 align:middle line:84%
We want datetime, column
name plus 978307200,

00:00:17.666 --> 00:00:21.656 align:middle line:84%
comma single tick
unixepoch single tick.

00:00:21.656 --> 00:00:25.196 align:middle line:90%
Local time is up to you.

00:00:25.196 --> 00:00:28.716 align:middle line:84%
Now, the only time
I use local time--

00:00:28.716 --> 00:00:29.886 align:middle line:90%
two instances here.

00:00:29.886 --> 00:00:33.066 align:middle line:84%
The first, if I'm doing
validation on my own device--

00:00:33.066 --> 00:00:35.046 align:middle line:84%
I know I live in
Eastern Standard Time.

00:00:35.046 --> 00:00:39.606 align:middle line:84%
So like right now, I'm in the
recording studio in California.

00:00:39.606 --> 00:00:43.806 align:middle line:84%
If I tried to do testing
from my local device,

00:00:43.806 --> 00:00:45.666 align:middle line:84%
it would confuse me
because I would have

00:00:45.666 --> 00:00:47.501 align:middle line:90%
to think Pacific to Eastern.

00:00:47.501 --> 00:00:49.626 align:middle line:84%
So I would choose local
time so it would convert it

00:00:49.626 --> 00:00:52.296 align:middle line:84%
for me because I know where
I was when I did my testing.

00:00:52.296 --> 00:00:54.149 align:middle line:84%
Now, if I did the
testing here, I

00:00:54.149 --> 00:00:56.316 align:middle line:84%
would also want to do local
time for here because it

00:00:56.316 --> 00:00:58.086 align:middle line:90%
would choose Pacific.

00:00:58.086 --> 00:01:01.426 align:middle line:84%
If you don't choose that,
it's going to put it in UTC.

00:01:01.426 --> 00:01:05.036 align:middle line:84%
You are going to see
your VMs are UTC.

00:01:05.036 --> 00:01:08.169 align:middle line:84%
So if you don't choose
local time, that's fine.

00:01:08.169 --> 00:01:09.836 align:middle line:84%
Then we do the same
thing a second time.

00:01:09.836 --> 00:01:15.806 align:middle line:84%
We do datetime column name
plus 97837200 comma single tick

00:01:15.806 --> 00:01:18.118 align:middle line:90%
unixepoch single tick comma.

00:01:18.118 --> 00:01:20.076 align:middle line:84%
And I'm going to show
you what this looks like.

00:01:20.076 --> 00:01:22.586 align:middle line:84%
So here we have all of
the converted timestamps.

00:01:22.586 --> 00:01:24.426 align:middle line:90%
They look good.

00:01:24.426 --> 00:01:25.716 align:middle line:90%
This is Mac.

00:01:25.716 --> 00:01:27.066 align:middle line:90%
I'm going to show you Unix.

00:01:27.066 --> 00:01:30.346 align:middle line:84%
And I'll make some
mistakes for fun.

00:01:30.346 --> 00:01:33.566 align:middle line:84%
So I'm going to say
datetime, and I'm

00:01:33.566 --> 00:01:35.856 align:middle line:84%
going to hopefully
spell it right.

00:01:35.856 --> 00:01:37.703 align:middle line:84%
Update time is my
timestring or my column.

00:01:37.703 --> 00:01:40.036 align:middle line:84%
And you notice how it says
modify or modifier, modifier,

00:01:40.036 --> 00:01:42.526 align:middle line:84%
modifier-- depends
on what it is.

00:01:42.526 --> 00:01:43.776 align:middle line:90%
So let me do it wrong.

00:01:43.776 --> 00:01:49.146 align:middle line:84%
I'm going to do it as a Mac,
978307200 comma single tick

00:01:49.146 --> 00:01:49.926 align:middle line:90%
unixepoch.

00:01:55.266 --> 00:01:57.758 align:middle line:84%
And if you look at these
timestamps, here's the issue.

00:01:57.758 --> 00:01:58.966 align:middle line:90%
It doesn't give you an error.

00:01:58.966 --> 00:02:01.916 align:middle line:90%
It's like yeah, 2047 at 11/11.

00:02:01.916 --> 00:02:03.096 align:middle line:90%
No.

00:02:03.096 --> 00:02:04.359 align:middle line:90%
That's not right.

00:02:04.359 --> 00:02:06.151 align:middle line:84%
So now you go back,
and you're like, great.

00:02:09.826 --> 00:02:12.466 align:middle line:84%
So you just do the column,
and you're like, OK,

00:02:12.466 --> 00:02:14.116 align:middle line:84%
that looks a little
more legible.

00:02:14.116 --> 00:02:18.196 align:middle line:84%
So we did datetime
column name unixepoch.

00:02:18.196 --> 00:02:20.866 align:middle line:90%
Here we go.

00:02:20.866 --> 00:02:23.556 align:middle line:84%
Now, what if I thought it
was 13 digits in length?

00:02:23.556 --> 00:02:28.812 align:middle line:90%
13 digits means divide by 1,000.

00:02:28.812 --> 00:02:32.186 align:middle line:90%
1/18/1970-- not quite right.

00:02:32.186 --> 00:02:35.126 align:middle line:84%
I would be lying to you
if I did not tell you,

00:02:35.126 --> 00:02:37.746 align:middle line:84%
for every single
query I wrote about 5

00:02:37.746 --> 00:02:40.166 align:middle line:84%
and 1/2 years ago, this is
what I did every single time.

00:02:40.166 --> 00:02:42.926 align:middle line:84%
I was like, umm, I'm
going to try them all.

00:02:42.926 --> 00:02:44.276 align:middle line:90%
Don't do that.

00:02:44.276 --> 00:02:46.586 align:middle line:84%
What I recommend is,
if you need something--

00:02:46.586 --> 00:02:48.574 align:middle line:90%
and I will actually--

00:02:48.574 --> 00:02:49.616 align:middle line:90%
I will make note of this.

00:02:49.616 --> 00:02:52.616 align:middle line:84%
I'm going to put
this in your Dropbox.

00:02:52.616 --> 00:02:53.936 align:middle line:90%
Lee Crognale did--

00:02:53.936 --> 00:02:56.666 align:middle line:84%
I have a lunchtime show called
Life has no ctrl alt del.

00:02:56.666 --> 00:03:00.236 align:middle line:84%
It won the Forensic
Forecast Award in 2021.

00:03:00.236 --> 00:03:01.856 align:middle line:84%
Lee did a presentation
on it where

00:03:01.856 --> 00:03:03.866 align:middle line:84%
she showed all
the different ways

00:03:03.866 --> 00:03:07.466 align:middle line:84%
times can exist in a database
and how to convert them.

00:03:07.466 --> 00:03:11.606 align:middle line:84%
She created a zip file that has
practice databases, the queries

00:03:11.606 --> 00:03:14.126 align:middle line:84%
that you need to run, and
just example walkthroughs.

00:03:14.126 --> 00:03:16.076 align:middle line:84%
I'm going to put
that as bonus items

00:03:16.076 --> 00:03:20.186 align:middle line:84%
in your course
Dropbox for day one.

00:03:20.186 --> 00:03:21.919 align:middle line:84%
This course is
taught in sections.

00:03:21.919 --> 00:03:23.336 align:middle line:84%
You will see the
Dropbox will say,

00:03:23.336 --> 00:03:25.586 align:middle line:84%
like, day one, day two, day
three, day four, day five,

00:03:25.586 --> 00:03:30.076 align:middle line:84%
day six, add-ons, gas, all
kinds of different things.

00:03:30.076 --> 00:03:32.963 align:middle line:84%
Associate the section to
a day, and look there.

00:03:32.963 --> 00:03:35.296 align:middle line:84%
And you'll find it, so you'll
get more practice with it.

00:03:35.296 --> 00:03:36.346 align:middle line:90%
It's a really good show.

00:03:36.346 --> 00:03:38.116 align:middle line:90%
You can also watch the show.

00:03:38.116 --> 00:03:40.816 align:middle line:84%
If you go out to Life
has no ctrl alt del,

00:03:40.816 --> 00:03:43.456 align:middle line:84%
and you google SQLite,
you'll see all the SQLite

00:03:43.456 --> 00:03:45.079 align:middle line:90%
shows that ever existed.

00:03:45.079 --> 00:03:46.996 align:middle line:84%
They're 30 minutes in
length, and they're well

00:03:46.996 --> 00:03:47.866 align:middle line:90%
worth your time.

00:03:47.866 --> 00:03:50.656 align:middle line:90%
Take a look at it.

00:03:50.656 --> 00:03:52.466 align:middle line:90%
So the difference--

00:03:52.466 --> 00:03:59.926 align:middle line:84%
Unix Epoch, Mac
with the 97837200--

00:03:59.926 --> 00:04:01.156 align:middle line:90%
crystal clear, right?

00:04:01.156 --> 00:04:03.406 align:middle line:84%
This is the point where I
wish I could see your faces,

00:04:03.406 --> 00:04:04.786 align:middle line:84%
and you're, like, what
is happening to me?

00:04:04.786 --> 00:04:05.431 align:middle line:90%
She's insane.
