All language subtitles for 3. EXTRACT

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