Data In Different Time Slices

Hi ,

In this article I will tell you the differences between time expressions and how they are used .

TIMEZONE PARAMETER

How TIME_ZONE is updated :

  • By giving the region name ,

  • By giving full time ,

  • By giving the operating system time zone ,

  • Time zone of the database .

ALTER SESSION SET TIME_ZONE= ‘+03:00’;

ALTER SESSION SET TIME_ZONE=dbtimezone;

ALTER SESSION SET TIME_ZONE=local;

ALTER SESSION SET TIME_ZONE= ‘TURKEY’;

PRESENT TENSE

CURRENT_DATE : Returns the current date in the user session and is of DATE type .

CURRENT_TIMESTAMP : Returns the current time and date in the user session . TIMESTAMP WITH TIME ZONE (timezone_hour , timezone_minute) .

LOCAL_TIMESTAMP : Returns the current date and time . Timestamp (year , month , day , minute , second) type .

  • Now let’s make an example to reinforce what we have written above :

DATE AND TIMESTAMP DIFFERENCE

TIMESTAMP COMPARISON

  • Now we create a date table and use different timestamps to compare them :

INTERVAL DATA TYPES

Interval data types are used to either store the differences between the data in the two datetime types , or to add or subtract time to the data .

  • INTERVAL YEAR TO MONTH —> Year and month

  • INTERVAL DAY TO SECOND —> Day , hour , minute , second

They are divided into two .

INTERVAL YEAR TO MONTH EXAMPLE :

INTERVAL DAY TO SECOND EXAMPLE :

EXTRACT

  • Allows to allocate some time periods of data of datetime type .

  • As you can see in the example above , we have listed the hire_date column separately as day , month , year using extract .

TZ_OFFSET

  • It is the expression that allows to show time zones .

TO_YMINTERVAL

  • It allows us to shift dates by giving year and month offset .

TO_DSINTERVAL

  • It allows us to shift dates by giving offset of day , hour , minute and second .

See you in my next post.