query

oracle date and time functions



Sharing buttons:

in this section let's look at oracle

state functions and see what kind of

queries you generally fire and what

functions you usually use when when you

run sequel statements which have date

related logic in them the first function

that i want to disk is is something that

we've already seen when we discussed

functions in their inputs it's the state

function so when i say select state from

dual i get the current system later the

current database date which is 30th

november 2013 there is already an

important point to understand here when

we say oracle state data type it

actually has both the date plus the time

after the seconds now one might wonder

why we are not seeing the time component

over here when date has the time

component the reason for that is again

the session level settings that we

currently have the display level

settings so let me first prove that this

doesn't have this actually has the time

component so if we say where state is

equal to to date of 30th November 2013

so technically if it doesn't have the

day this should be the same but if you

see here the nor it nor Rose return

which means this condition has actually

failed so how do we make sure that we

see the entire date including the time

component as well there are multiple

ways to do it

one thing that is that is more of a

programmatic way to do it it's a is

altering the session to show the date

format and you want this is again

something that we have done in a really

old video when it comes to filters but

let's do it again over here so you can

say alter session set nls date format is

equal to DD mo n DD MN or Y by H H I

want to see the data in 24 hour format

mi SS

so if you can see here it says the

session has been altered

now when I say state from dual as long

as I am logged in in this particular

session I will see the time component as

well

it says 943 basically and because it is

in the 24-hour format so this is how we

confirm that Oracle has the date format

so what happens when let's say I

disconnect this session and again run it

and again connect I'm going to use the

OE over here and when it on the same

thing again the settings have been reset

so when you're running specifically

inside a script for example you always

if you want the date format to be in a

specific way in the beginning of the

script you can put this condition saying

you can put this command saying order

session my date format should always be

this when you're working in sequel

developer though you have this option

inside preferences tools preferences go

to database and then nls and here you

see that the date format is DDM ONRR

what I also want to see actually I want

to set this to DD m1 why why why why

h/h 24 in my missus so this is the date

format that I am most familiar with and

I want data to be in displayed in this

particular format so when it will ok and

say selects is date from dual you see

the data in the format that you need

once again you but you need to remember

is internally the date data type is

represented pretty much the same it's a

inbuilt data type what we are changing

here is only the way it is displayed

when you're running a query so a lot of

times when users query data they might

also have this confusion saying hey I'm

saying my where clause or my where

condition is equal to so and so date

when I see the data here it says 30th

November but when I said the date equal

to 30th November I get no results the

reason for that is this date that

they're seeing here is not the complete

date it also has the time component and

you need to make sure your queries are

changed to adjust to that particular

factor the second important thing to

remember is that Oracle's date

arithmetic works at day level what does

it actually mean so here we have the C

state as 30 M

2013 let's say I want a date which is

one day ahead of this so I want to add

one day to this particular day I can

just do sustained +1 and instead of xxx

I get first December at the same time so

all we are adding here is just exactly

adding 24 hours to this particular day

so let's say instead of 24 hours I just

want to add one hour so in that case you

could do 1 by 24 here you get the same

date with one hour added if I want to

add one minute because an hour as 60

minutes you can say by 60 and if you

want to add one second you do another 60

out after so once you understand Arcas

data dramatic works at a day level

writing queries like this becomes really

simple so for example what I could do is

if somebody asks me what are the orders

that have been placed in the last 30

days so I could say select star from Oh

a dot orders where order date is greater

than 30 a day which is 30 days in the

past so you can say this game current

name - critic anything placed after that

you want to see those orders so this is

how you would do plain simple data

arithmetic inside Oracle trunk is

another really useful function when it

comes to date arithmetic and running

queries where your which are based on

dates so we've seen the example of trunk

being an overloaded function in the

numbers chapter in the case of numbers

when you did shrunk it removed the

decimal points up to whatever you

specified in the case of dates it works

very much the same way but instead of

removing a number it removes a

particular date or time component for

example when you say select state from

do you get all the you get the time

component here by default when you say

trunk Oracle gets rid of the time

component and gives you that date at 0 0

hours

just like numbers here also you can pass

in multiple inputs to say till what part

you want the date to be truncated for

example the default here is till till

the date component you could say I want

data to be truncated till the month

component so the instead of 30th

November Oracle gives you a date back

saying first number of 2013 so the

entire month part has been truncated now

and you get the first of the month you

could do the same thing with here as

well and here you get in the entire

years component or the entire years part

of the data strung cater and you get 1st

Jan 2013 so this is how you can use the

trunk function inside dates and I hope

this gives you a decent overview of how

overloaded functions work the same

function depending on the data type will

behave differently again let's say this

is really useful when you're running

queries where you want to see the orders

that are placed in the year 2013 so you

can do two things one is you can say

select star from way down orders where

order date greater than to date 0 1 Jan

because 13

so this query I'm sorry so this query

works as well the problem with this is

this query works only for only for now

let's say we go into 2014 then you would

probably want to see all the orders that

have been placed after first Jan 2014

and you probably changed want to change

the query again this is not a very

reusable query in that in that sense so

what we actually can do we say trunk of

state come why why why why now when you

run the query you see all the orders

that are that have been placed after

2013 when you run the same query on any

date in 2014 you see all the orders that

have been placed after first Jan 2014 so

in that sense it is a reusable query

where your query condition actually

changes based on when you're running it

so instead of passing in fixed dates

this is more of an approach that you

will see in a lot of scripts in a lot of

sequel processes that are at your

company or the sequel process that you

write this is probably more of the

approach that you would take to write

generic scripts this is also another

really important reason why you should

clarify requirements when somebody is

asking for a set of data for example

when somebody asks data that have been

placed in the current year or all the

orders that have been placed in the past

year or so it's not really a very

precise requirement you need to

specifically ask them are you looking at

data that is all the orders that have

been placed in the last 365 days or

everything that has been placed in the

year so far

because if they're looking for years

orders that have been placed in the year

so far then this is the right function

if they're looking at orders that have

been placed in the last 365 days then

you might want to say sustained minus

365 the output for these two queries is

substantially different so you need to

really confirm the requirements and say

what kind of data that you want to see

before you write the query for that

particular question we just saw examples

of adding minutes or seconds or or days

to a particular date that was a little

easier because every date every every

day has 24 hours and every hour has

precisely 60 minutes but what do you

have to do when you are trying to add

one month of been trying to get date

which is one month ahead from now or one

year ahead from now for example let us

say if I say select see state plus 30

from dual this is a good enough way of

saying the next month all the time it's

not because a month doesn't necessarily

always have 30 days it might have 31 it

might have 28 or it might have 29 the

same thing goes for ears as well you

can't always add 365 days to the current

date and say well this is the next year

state in the same fashion sometimes it

works sometimes you might have 366 days

in which case your logic is messed up so

to help with this logic where based on

the ear or based on the month you add

different dates or will has some inbuilt

functions provided one of them is add

months so the function takes in two

parameters one is a cat one is a date

and the other is the number of months

that you want to add to it

for example required three months to see

state you will get 28 February 2014

though currently it is 30th November it

will give you the last name of that

particular month and adjust it

accordingly to get the correct date you

could also say that I want to add two

years which is 2 into 12 months and you

get 30th November of 2015 so this is

another example of where before you

write a function by yourself before you

write an entire logic in your PL sequel

function you should look for Oracle

functions which are already providing

the logic chances are if it's a decent

enough requirement if it's a common

enough requirement Oracle already has a

function for you so we've seen examples

of how you can add different types of

intervals where you can add a day a

month a year or a second and so on using

somewhat of a rudimentary method where

for adding a particular second you have

to say 1 by 24 by 60 by 365 and so on

and so forth the reason why I explained

them to you is because there might still

be a lot of scripts a lot of logic which

uses on those old-fashioned methods in

your code and to understand that you

need to understand or can state

arithmetic but often late Oracle has

also Oracle also provides a couple of

very useful functions really simple to

understand functions which

to add or subtract any amount of days or

any amount of time intervals to a

particular date and though our and those

are the num to come to D s interval and

norm to ym interval this function here

it converts our given number to do

anything between a day in a second so if

you want to add a second and our order

date to a particular date then you can

use this function if you want to add a

month or a year you can use this

function so the usage of this is really

simple let's say if you want to add

select sustained let's see state and the

new column side-by-side so that we get

we can confirm the results as well and

then you say sustained now I want to add

an interval to it let's say I want to

add five hours to it you can same num2 d

s interval five hour

so in this case I get 9:57 is the time

here and here the after adding five

hours it's 1457 the same thing can you

can do with everything up to a dates so

I can do seconds hours minutes and of

course days so this kind of helps you

avoid all that clumsy logic where you

say 1 by 24 1 by 24 by 60 and so on but

it's always good to know that trick as

well because there are there is certain

there is some code out there which

already uses that that logic the second

one is pretty simple you can add months

or years using num2 i am interval so you

can say add five months you get data

which is five months from now and you

can say add five years you can data

which is five years from now so this is

how you can do almost pretty much all

the other data and metric when adding

intervals or two dates in Oracle using

these two functions so far we've

discussed about eight to nine different

Oracle daytime functions and the easiest

way to really get familiar with this to

really understand this and know which

function to use when is writing as many

queries as possible and you also need to

remember that this function does not

have each statement or each expression

doesn't necessarily have to be used only

once in a particular function or an

expression doesn't necessarily have only

one particular date or character any

such function used more often than not

you will probably use multiple functions

on the same expression to get the data

in the in the format that you need so

let's take a couple of examples where

you have to use multiple functions and

see how we'll go about getting data for

them so let's try and solve a problem

where we can use different functions

that we've learned so far

things like truncate on a date things

like aggregate functions and so on and

so forth one common example is when you

want to get a yearly breakdown of your

total sales so in my table orders so

let's start from your own orders

I have the order date saying 16th August

1999 16 19 November and so on and so

forth and I have the order total but

what the business wants to see is see

for a given year for example in 2000

what is the total order amount let's say

it is 200,000 in 1999 what is the

corresponding order them on something

like this so this is the format that

they want to see in where the data is

aggregated and you get one total per

year so how do we go about it the first

thing that immediately should strike is

that in the orders table you have for a

year you have hundreds of records but

what you want to do is consolidate all

of them into one record per year

which means you need to use a grouping

function so let's try writing the group

by here group by I want to see order

date and here I want to say order date

and the total value of my orders so when

you run this instead of seeing one row

per year what you see here is that you

see one row per order time this is

because when you're grouping by these

different orders have different times

they have different dates all we're

looking for is aggregating data to the

air level so for this particular example

we need to remove all the other

components other than the year component

so that's why we would say trunk bye bye

bye bye and then even the group bye we

need the same expression chunk of order

date going by one now as you can see

except for the ear part everything else

in the date has been truncated off so

you see first chan of that ear and the

corresponding sales so this is all the

sales which happen in 1999 these are

sales which happened in 2000 and so on

and so forth so let's actually order

these by the ear descending because it

will make it will actually make more

sense

so trunk descending

in this case again if somebody is

looking at this data it might not make

as much sense or you might be conveying

some wrong information saying on the

first Jan 2011 of orders that have been

placed this is a little incorrect it's

not really accurate what we're trying to

say is that in the year 2000 this is the

total set of this a total amount of

orders that we've got so here we only

want to display the year let me put the

same thing in the group by and other

places inside my sequel as well

you

there you have it the breakdown of the

total amount of orders that we've got

the total revenue from each of from all

the orders in a particular order by the

ear descending you can of course label

them in however you want to make the

data look more meaningful but this is

how you can use multiple functions some

with date some with character some with

grouping to get the data in the format

that you need again here in this case we

have used a function called two card

which is actually a conversion function

which we'll be discussing next but this

is just to show that you can use

multiple functions together to really

effectively get the data in one

particular format another example that I

want to go through which kind of

requires you to use multiple functions

is generating monthly statements for

example let's say currently I am working

in a company where I want to say all the

states that have happened in the last

month so when any time during this month

any time during the month of November

2013 when I run a report I want data

from first October 2013 to 31st October

2013 summarized according to whatever

requirements I have maybe it is the

total orders maybe it is total revenue

total profit whatever the requirements

are but in any time during November when

I run I need these two dates as the

beginning and end date for my data so

the one thing that you can do is let's

say when learning assist aid from dual I

get 30th November so how do we get the

first of previous month so you can say

admins of C state comma minus 1 which

will give you the same date of the

previous month and when you put a trunk

truncal month you get the first October

2013 which is the first day of the

previous month you can do the same thing

with last day of last day of previous

month as well maybe you could do you can

get chunk of this month and then do last

day which is another orbital function we

say last day of that month you get 31st

October 2013 and in your query now you

can say data between chunk of this which

is the first October and last day of

this which is the 31st October and

you'll get data in the time range again

there are various ways of implementing

the same problem so instead of last day

I can use this is well here is a trunk

of cyst 8 comma ml which will give me

the first date of the current month

and I could say minus one here which

will again give me the last day of the

previous month so there multiple ways of

running the same of getting the same

data implementing the same logic and you

can verify them by running statements

against DLN and seeing how they are and

then change your queries to use these

particular variables or these particular

computations