LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon