MySQL: Updating the Configuration of a MySQL DB System on OCI
Overview: A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to ... Read More
Découvrez pourquoi Eclipsys a été nommée 2023 Best Workplaces in Technology, Great Place to Work® Canada et Canada's Top 100 SME !
En savoir plus !In this blog, I’ll demonstrate the use of SUM and AVG functions with INTERVAL datatype
Prerequisites:
The example in this blog requires the following table
drop table if exists trips purge; create table trips ( id number, start_time timestamp, end_time timestamp, duration interval day to second generated always as (end_time - start_time) virtual ); begin insert into trips (id, start_time, end_time) values (1, timestamp '2024-01-20 08:45:00.0', timestamp '2024-01-20 18:01:00.0'); insert into trips (id, start_time, end_time) values (2, timestamp '2024-01-22 09:00:00.0', timestamp '2024-01-22 17:00:00.0'); insert into trips (id, start_time, end_time) values (3, timestamp '2024-01-25 08:00:00.0', timestamp '2024-01-25 17:45:00.0'); insert into trips (id, start_time, end_time) values (4, timestamp '2024-01-27 07:00:00.0', timestamp '2024-01-27 16:00:00.0'); insert into trips (id, start_time, end_time) values (5, timestamp '2024-01-28 07:00:00.0', timestamp '2024-01-28 16:00:00.0'); insert into trips (id, start_time, end_time) values (6, timestamp '2024-01-29 07:00:00.0', timestamp '2024-01-29 16:00:00.0'); insert into trips (id, start_time, end_time) values (7, timestamp '2024-01-30 07:00:00.0', timestamp '2024-01-30 16:00:00.0'); insert into trips (id, start_time, end_time) values (8, timestamp '2024-01-31 07:00:00.0', timestamp '2024-01-31 16:00:00.0'); commit; end; /
1. If we use SUM or AVG functions on an INTERVAL datatype on pre-23c Oracle database, we will get an error as shown below.
2. Oracle 23c database allows the use of SUM and AVG functions with INTERVAL datatype
select sum(duration),avg(duration) from trips;
– We can also use SUM and AVG as analytics functions with INTERVAL datatype
select id,start_time,end_time,duration, sum(duration) over (order by id rows unbounded preceding) as DUR_RUN_TOTAL, avg(duration) over (order by id rows unbounded preceding) as DUR_RUN_AVG from trips;
Overview: A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to ... Read More
Overview: A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to the my.ini or my.cnf ... Read More