To my surprise, doing something like this is not possible in (PL)SQL: select avg(a_date) from a_table
This is a relatively elegant workaround: with t as ( select (sysdate + 1) as d from dual union all select (sysdate - 17) as d from dual union all select (sysdate + 4) as d from dual ) select to_date('1','J') + avg(t.d - to_date('1','J')) as avg_date from t

Don't use this to "truncate a timestamp into a date": trunc(t.trx_timestamp) But use: to_date(trunc(t.trx_timestamp)) In "normal" queries the first method works, but it won't work correctly in views.

For "non-truncated" days, use: cast(t.trx_timestamp as date)

Of course you can use something like this: select systimestamp - to_timestamp('01-01-2010','DD-MM-YYYY') as d from dual But that gives you an interval, which I almost never need.

A nicer way to do it: select cast(systimestamp as date) - cast(to_timestamp('01-01-2010','DD-MM-YYYY') as date) as d from dual Which gives you a number of days, which is much more useful.


To get the difference between timestamps in seconds, with all the millisecond precision intact: declare x timestamp := systimestamp; y timestamp := to_timestamp('01-01-2010','DD-MM-YYYY'); begin dbms_output.put_line( extract(day from (x-y))*24*60*60 + extract(hour from (x-y))*60*60 + extract(minute from (x-y))*60 + extract(second from (x-y)) ); end;
Result: 483442898.625437 seconds

Unix (Epoch) time is the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, minus leap seconds.

To calculate the number of milliseconds elapsed since then, taking your timezone into account, use this query: select (cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 24*60*60*1000 + to_number(to_char(systimestamp,'FF3')) as m from dual;
Result: 1745746898626 milliseconds

Conversion of a date from a timezone to the "database timezone": cast((from_tz(cast( some_date as timestamp ), sessiontimezone) at time zone dbtimezone) as date)
and vice versa: cast(to_timestamp_tz( some_date, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM' ) at time zone dbtimezone as date)

If you use something like to_date('2024',YYYY'), you have only a 1 in 12 chance of getting the result I expected (1st Jan 2024).
Any other month will mess it up - only in January will the result be as I expected, in e.g. August you will get 1st Aug 2024.

The comment by Connor on https://asktom.oracle.com/ords/asktom.search?tag=to-date-with-no-month states:

I havent seen a documentation reference that explicitly states this rule (but its been this way for as long as I've used oracle).
But it makes sense (to me :-)) to have the rules like this because you'll always get back a valid date.
If you used "current day" not "first of month" then something like:
to_date('2015-feb')
would work on January 25th but fail if you ran it on January 31st.

I don't think I agree with that one...

Note the YYYY-DD example (also given in the post above): select to_date('2015-11', 'YYYY-DD') from dual; --> 11-sep-2015 - I rest my case :-)
The final MM-DD example returns a result in the current year, which sort of makes sense. Although an exception or using 1970 would be just as (il)logical I suppose.

Using to_char to convert a timestamp into a string can produce different results in SQL and PL/SQL.

in SQL this returns 20240704062506.298731: select to_char(systimestamp, 'YYYYMMDDHH24MISSxFF') from dual;
in PL/SQL this returns 20240704062506.298731000: p_ts := to_char(systimestamp, 'YYYYMMDDHH24MISSxFF');
The documentation about the FF format model elements states: "If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision."
Apparently this is not the same for SQL and PL/SQL - not by default anyway, your database may be set up differently.
If you do specify a digit, e.g. FF6 instead of FF, it works as expected.

I noticed this when I got a "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" exception because of this.
As you can imagine, this took a while to pinpoint and debug...