Original subtitles

0 Extract function will help us extract parts 1

from a date or a time 2

so if you have a timestamp 3

Extract can help you get any part of that timestamp out 4

So 5

this is the index. You write extract then you specify a unit which you want from the date 6

So you will have to use this keyword from 7

When you are using extract 8

What are the different types of units 9

Majorly these are the types of units used 10

There are Other units also 11

But these are the most commonly used ones 12

If you want to find out 13

Which day of the month it is 14

From the given date you can use the day unit 15

If you want to find out 16

The number of seconds since 17

1st Jan 1970 18

Epoch function is used. We will see why this epoch function is important in the example 19

If you want to find out the hour part only 20

Of that timestamp 21

You can use hour. if you want minutes you can use minute if you want month second year and so on 22

Let us see an example 23

If you want to take out the day 24

From this date 25

So it'll give you 26

It is the 25th day of the month so you'll have to mention 27

Day from the date 28

Day from the timestamp of any date 29

If you want minutes you'd write minute from the time 30

Now if you remember in our sales table 31

We had ship date and order date 32

When we did age 33

We received a string with said 34

so many days were taken. you cannot do mathematical operations on that string says it is a 35

String 36

If you want difference between the two dates 37

As number of seconds number of hours or number of days so that you can further work on that number 38

This extract function will help you get that 39

So when we select order line and extract 40

epoch from 41

Ship date minus order date 42

What will this give me. ship date - order date will give me the difference of these two dates 43

If I 44

do epoch now on this date I'll get the difference 45

Between these two dates in seconds 46

When I have the seconds I can convert those seconds into minutes 47

I can Convert those minutes into 48

hours by dividing by 60. i can convert those hours into days by dividing by 24 49

So that number 50

Of seconds can be used 51

To do any type of mathematical 52

Function. once you have the number of days you can then use the comparison operator to find out which 53

Order took more than 2 days more than 3 days 54

Those kind of things you cannot do on a string type of output 55

So let us go 56

And run these queries 57

So let us find out 58

the 59

day 60

For the current date. so we will select 61

Extract 62

Day from 63

The current 64

Date 65

Let us run this query 66

And we will see it is the 67

27th day of the month 68

Similarly if you want to extract the hour 69

Of this 70

day 71

We will select current timestamp 72

And extract 73

hour from the 74

Current timestamp 75

So that we can compare that both are giving us 76

The same answer 77

So it is the 78

14th hour of this day 79

That is what 80

hour 81

Extracted from the timestamp is giving us 82

Now let us find out the difference between two dates in seconds 83

For that we will be using the epoch function 84

Let us write select 85

order date 86

Ship date 87

And 88

extract 89

Epoch from then within this we will 90

Mention the difference of these two dates 91

So ship date minus 92

Order date 93

From 94

Sales table 95

Let us run this 96

So this is giving an error 97

Because when 98

We subtracted ship date minus order date 99

It gave an output of integer 100

And when we are finding epoch of this integer 101

It is giving an error it should be a date 102

So let us rectify this 103

We will do epoch from ship date and then minus epoch from order date 104

Let us write select order date 105

comma ship date comma 106

Extract 107

Epoch from ship date 108

minus extract 109

epoch from 110

Order date 111

This whole thing I want in one column 112

From the table sales 113

Let us name this as 114

Seconds taken 115

sec taken 116

From 117

Sales table 118

when we run this 119

When order date was 120

8th November 2016 and ship date was 11th November 2016 there the difference of 3 days 121

3 days into 24 hours into 60 minutes into 60 seconds will give us this many 122

Seconds 123

If you had exact timestamp in the order date and exact timestamp in the ship date 124

You will get the exact seconds 125

Between these two 126

timestamps 127

Now that we have the seconds 128

You can convert this seconds into minutes into hours into days 129

By dividing it by 60 60 24 and so on 130

And you can then order it by numeric value 131

You can also put operators 132

Say if you have it in days 133

So if you want to find out 134

for which orders you took more than 3 days 135

You can 136

Take out those order separately 137

All those operations can be done 138

Using this 139

Extract operator on the datetime 140

These are all the functions we'll be discussing in datetime 141

And this will majorly cover anything you will be doing in your day-to-day academic or professional life