Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

DATETIME Datatype And TIMESTAMP In Oracle

Leave a Comment / SQL, DATETIME / By

DATETIME Datatype And TIMESTAMP In Oracle

What is Oracle datetime datatype?

Datetime data type stores the values that consists of both date and time.

Date data type doesn't include time values.

Date Time format in Oracle:

  • CENTURY
  • YEAR
  • MONTH
  • DAY
  • HOURS
  • MINUTES
  • SECONDS
  • SYSDATE is the function used to represents the current database date and time.
  • And default date format is DD-MON-RR
Example:
select to_char(sysdate, 'DD-MM-RRRR HH24:MI:SS') CURRENT_DATE from dual;
Century -> 20
Year    -> 20
Month   -> 08
Day     -> 09
Hour    -> 16
Minute  -> 59
Second  -> 21

The Datetime data types are:

  • TIMESTAMP
  • DATE
  • TIMESTAMP WITH TIMEZONE and
  • TIMESTAMP WITH LOCAL TIMEZONE
  • INTERVAL DAY TO SECOND
  • INTERVAL YEAR TO MONTH
  • SYSDATE
  • CURRENT_DATE
  • EXTRACT
  • CURRENT_TIMESTAMP
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • SYSTIMESTAMP
  • TO_DSINTERVAL
  • TO_YMINTERVAL
  • LOCALTIMESTAMP
  • TO_TIMESTAMP
  • FROM_TZ
  • TO_TIMESTAMP_TZ
  • DBTIMEZONE
  • SESSIONTIMEZONE
  • TZ_OFFSET

Date Arithmetic:

OperationResultDescription
Date + NumberDateAdds a no of days to a Date
Date – NumberDateSubtracts a no of days to a Date
Date – DateNo of DaysSubtracts one date from another
Date + Number/24DateAdds no of hours to a Date
How can I change datetime in Oracle?

Example:

select sysdate current_date from dual;

In the above example the sysdate function returns the current date.

select sysdate + 7 future_date from dual;

In the above example the future_date column is returning the date which is 7 days ahead of current date.

select sysdate - 7 past_date from dual;

In the above example the past_date column is returning the date which is 7 days before of current date.

select round(sysdate - to_date('01-Aug-20', 'DD-MON-RR')) date_diff from dual;

In the above example the date_diff column is returning the days difference between current date and 01-Aug-2020. And its value is number.

select sysdate + 17/24 future_date from dual;  --Adding hour to current date
Calculate no of weeks and no of months between two dates:
select round((sysdate - to_date('09-Aug-2019', 'dd-mon-rrrr'))/7) no_of_weeks from dual;
select round((sysdate - to_date('24-jun-2019', 'dd-mon-rrrr'))/30) no_of_months from dual;

DATE FUNCTIONS:

FunctionsDescription
MONTHS_BETWEENNo of months between two dates
ADD_MONTHSAdd calendar months to date
NEXT_DAYNext day of the date specified
LAST_DAYLast day of the month
ROUNDRound date
TRUNCTruncate date
Example:
select round(months_between(sysdate, to_date('23-jun-2019', 'dd-mon-rrrr'))) no_of_months 
from dual;
select add_months(sysdate, 4) future_date from dual;
select add_months(sysdate, -4) past_date from dual;
select next_day(sysdate, 'Sunday') next_sunday_date from dual;
select next_day(sysdate, 'Tuesday') next_tuesday_date from dual;
select last_day(sysdate) lastday_date from dual;
alter session set nls_date_format = 'dd-mm-rrrr hh24:mi:ss';
select sysdate current_date from dual;
select round(sysdate) rounded_current_date from dual;
select round(sysdate, 'year') rounded_current_year_date from dual;
select round(sysdate, 'month') rounded_current_month_date from dual;
select round(sysdate, 'day') rounded_current_day_date from dual;
select trunc(sysdate, 'year') truncated_current_year_date from dual;
select trunc(sysdate, 'month') truncated_current_month_date from dual;
select trunc(sysdate, 'day') truncated_current_week_date from dual;

Date Conversion Function:

  • TO_CHAR
  • TO_DATE

TO_CHAR:

Its converts date to character. So final data type will be character.

Example:
select to_char(sysdate, 'dd/mm/rrrr') current_date from dual;
select to_char(sysdate, 'dd-mm-rrrr') current_date from dual;
select to_char(sysdate, 'ddmmrrrr') current_date from dual;
select to_char(sysdate, 'dd/mm/rrrr hh:mi:ss') current_date_time from dual;
select to_char(sysdate, 'dd/mm/rrrr hh24:mi:ss') current_date_time from dual;
select trim(to_char(sysdate, 'fmddth month rrrr')) current_date from dual;
select to_char(sysdate - 10/24, 'dd/mm/rrrr hh24:mi:ss am') past_date_time from dual;
select to_char(sysdate, 'fmddspth month rrrr') current_date from dual;
select to_char(sysdate, 'fmDdSpth Month rrrr') current_date from dual;

Spell out Day |Month| Year :

select to_char(sysdate, 'Day') current_day from dual;
select to_char(sysdate, 'Month') current_month from dual;
select to_char(sysdate, 'Year') current_year from dual;
select to_char(sysdate, 'Syear') current_year from dual;

Numeric Day |Month| Year|Hour|Minute|Second :

select to_char(sysdate, 'dd') current_day from dual;
select to_char(sysdate, 'mm') current_month from dual;
select to_char(sysdate, 'rrrr') current_year from dual;
select to_char(sysdate, 'hh') current_hour from dual;
select to_char(sysdate, 'hh24') current_hour from dual;
select to_char(sysdate, 'mi') current_minute from dual;
select to_char(sysdate, 'ss') current_second from dual;

Alternative ways for Numeric Day|Month|Year|Hour|Minute|Second:

Below formats are completely numeric.

select extract (day from sysdate) current_day from dual;
select extract (month from sysdate) current_month from dual;
select extract (year from sysdate) current_year from dual;
select extract (hour from cast (sysdate as timestamp)) current_hour from dual;
select extract (minute from cast (sysdate as timestamp)) current_minute from dual;
select extract (second from cast (sysdate as timestamp)) current_second from dual;
select extract(timezone_hour from current_timestamp) timezone_hour from dual;
select extract(timezone_minute from current_timestamp) timezone_minute from dual;
select extract(timezone_region from current_timestamp) timezone_region from dual;
select extract(timezone_abbr from current_timestamp) timezone_abbr from dual;

Three Letter abbreviation of the day and month:

select to_char(sysdate, 'DY') current_day from dual;
select to_char(sysdate, 'MON') current_month from dual;

Day of the Year, Month, Week:

select to_char(sysdate, 'ddd') day_of_current_year from dual;
select to_char(sysdate, 'dd') day_of_current_month from dual;
select to_char(sysdate, 'd') day_of_current_week from dual;
select to_char(sysdate, 'ww') week_of_current_year from dual;
select to_char(sysdate, 'w') week_of_current_month from dual;
select to_char(sysdate, 'q') quarter_of_current_year from dual;
select to_char(sysdate, 'scc') current_century from dual;
select to_char(sysdate, 'cc') current_century from dual;
select to_char(sysdate, 'DL') long_date_format from dual;
select to_char(sysdate, 'DS') short_date_format from dual;
select to_char(sysdate, 'PM') pm_format from dual;
select to_char(sysdate, 'AM') am_format from dual;
select to_char(sysdate, 'Ddth, Month, yyyy', 'NLS_DATE_LANGUAGE = HINDI') date_in_nls_language 
from dual;
select to_char(sysdate, 'SSSSS') second_passed_midnight from dual;
select to_char(sysdate, 'FMddth Month, yyyy') date_with_no_leading_trailing_blank 
from dual;

Datetime and Interval Datatypes:

Adding different Intervals to Current_Date:

select sysdate from dual;
select sysdate + interval '55' second second_interval from dual;
select sysdate + interval '55' minute minute_interval from dual;
select sysdate + interval '55' hour hour_interval from dual;
select sysdate + interval '55' day day_interval from dual;
select sysdate + interval '01 11:20:55' day to second day_to_second_interval 
from dual;
select sysdate + interval '55' month month_interval from dual;
select sysdate + interval '55' year year_interval from dual;
select sysdate + interval '14-11' year to month year_to_month_interval from dual;
select sysdate + to_dsinterval('11 00:11:35') day_to_second_interval from dual;
select sysdate + to_yminterval('11-10') year_to_month_interval from dual;
select sysdate + numtodsinterval('11', 'day') day_interval from dual;
select sysdate + numtodsinterval('11', 'hour') hour_interval from dual;
select sysdate + numtodsinterval('15', 'minute') minute_interval from dual;
select sysdate + numtodsinterval('45', 'second') second_interval from dual;
select sysdate + numtoyminterval('11','year') year_interval from dual;
select sysdate + numtoyminterval('55', 'month') month_interval from dual;

TIMESTAMP IN ORACLE:

What Timestamp means?

A Timestamp is an encoded information which indicates the occurrence of an event in terms of date and time.

It can store time upto a small fraction of second.

What is the format of Timestamp in Oracle?

The format of Timestamp is: DD-MON-YYYY HH24:MI:SS.FF

What is the difference between Date and Timestamp in oracle?

Date and Timestamp store the information about century, decade, year, month, day, hour, minute and second.

Timestamp store fractional seconds and fractional seconds with time zone.

SYSTIMESTAMP:

It returns the system's TIMESTAMP WITH TIMEZONE.
select systimestamp from dual;

LOCALTIMESTAMP:

It returns the local timestamp.
select localtimestamp from dual;

CURRENT_TIMESTAMP:

It returns the CURRENT_TIMESTAMP in TIMESTAMP WITH TIMEZONE format.
select current_timestamp from dual;
select sessiontimezone from dual;
select dbtimezone from dual;
select current_date from dual;

TZ_OFFSET:

It returns the time zone offset corresponding to the parameter passed.

Parameter may be a valid time zone name or DBTIMEZONE or SESSIONTIMEZONE.

select * from v$timezone_names;
select tz_offset('Asia/Calcutta') timezone_info from dual;
select tz_offset('America/Antigua') timezone_info from dual;
select tz_offset(sessiontimezone) session_timezone_info from dual;
select tz_offset(dbtimezone) db_timezone_info from dual;

TO_TIMESTAMP:

It convert a date or a character string to a TIMESTAMP data type.

select to_timestamp(sysdate,'dd-mm-rrrr hh24:mi:ss.ff') sysdate_to_timezone from dual;

TO_TIMESTAMP_TZ:

It converts a date or a character string to a TIMESTAMP WITH TIMEZONE data type.

select to_timestamp_tz(to_char(sysdate, 'dd-mm-rrrr hh24:mi:ss')||' 5:30', 'dd-mm-rrrr hh24:mi:ss tzh:tzm') sysdate_to_timestamp_with_timezone from dual;

FROM_TZ:

It converts a TIMESTAMP data type to a TIMESTAMP WITH TIMEZONE data type.

select from_tz(timestamp '2019-12-08 21:04:29', '5:30') timestamp_to_timestamp_with_timezone from dual;
select from_tz(localtimestamp, '5:30') timestamp_to_timestamp_with_timezone from dual;
select (date '2019-12-08') char_to_date from dual;

ORACLE-BASE-ARTICLE

OTHER TOPICS:
BULK BINDING IN ORACLE

Author

Leave a Comment

Your email address will not be published.