Date arithmetic with SYSDATE in Oracle
Common date calculations with SYSDATE, NLS date formatting and a query on active session history.
This cheat sheet collects the most common date calculations with SYSDATE in Oracle, along with date formatting per session and a query on active session history.
Calculations with SYSDATE
SYSDATE is expressed in days, so a fraction of a day represents hours, minutes or seconds.
| Expression | Meaning |
|---|---|
date_col > SYSDATE - 8/24 |
Past 8 hours |
date_col > SYSDATE - 30 |
Past 30 days |
date_col > SYSDATE - 30/1440 |
Past 30 minutes |
15/24/60/60 |
15 seconds |
1/24/60 |
One minute |
1/24 |
One hour |
TRUNC(SYSDATE + 1/24, 'HH') |
Next full hour |
Formatting dates
To have the client display dates in a given format, set the NLS_DATE_FORMAT environment variable:
export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"
Or change the format for the current session:
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'
To format a date on the fly:
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual
Active session history
The following query lists the activity of the last 8 hours from the active session history (AWR):
SELECT
h.sample_time,
u.username,
h.program,
h.module,
s.sql_text
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 8/24
AND h.user_id=u.user_id
AND h.sql_id = s.sql_id
ORDER BY h.sample_time desc
Notes
- The
8/24and1/24values in the source note had been altered (Aug-24,Jan-24) and were restored. - The
DBA_HIST_*views require the Oracle Diagnostics Pack license.