In this article I will tell you the differences between time expressions and how they are used .
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’;
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
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 :
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 .
It is the expression that allows to show time zones .
It allows us to shift dates by giving year and month offset .
It allows us to shift dates by giving offset of day , hour , minute and second .
See you in my next post.