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: (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') from dual But that gives you an interval, which I almost never need.

To get the (more convenient) difference between timestamps in seconds: 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: 433928577.298375 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: 1696232577303 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)