Oracle Convert Seconds to Hours:Minutes:Seconds
If you're just looking to convert a given number of seconds into HH:MI:SS format, this should do it
SELECT
TO_CHAR(TRUNC(x/3600),'FM9900') || ':' ||
TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00') || ':' ||
TO_CHAR(MOD(x,60),'FM00')
FROM DUAL
where x is the number of seconds.
oracle SQL convert seconds into date / time
That value looks like number of seconds elapsed since 1st of January 1970 (i.e. Unix epoch time). If so, then you could try with this:
(first, setting session date/time format, just to know what is what. You don't have to do that):
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
and then
SQL> select date '1970-01-01' + 1661596801 * interval '1' second result
2 from dual;
RESULT
------------------------------
27.08.2022 10:40:01
SQL>
which is today at 10:40:01 in the morning.
how to convert HH:MM representation to minutes in oracle sql
If you require to Print 08:00 hours as 480 minutes,
Extract the Digit before : and multply with 60 and add the digit after :. So you can convert the HH:MM representation in to minutes.
SELECT REGEXP_SUBSTR(ATT.workdur,'[^:]+',1,1)*60 + REGEXP_SUBSTR(ATT.workdur,'[^:]+',1,2) MINUTES FROM DUAL;
difference between 2 dates in days, hours, minutes and seconds
Is there something like a
year(3) to second(0)?
It does not make sense to have a generic INTERVAL data type that contains years, days, hours, minutes and seconds.
If you have an interval of 365 days then if your interval starts on 2022-01-01 than that equals 1 year but if the interval starts on 2020-01-01 then is less than one year as there are 366 days in 2020 due to it being a leap year. Therefore, the number of days in a year depends on which year you are talking about and you cannot have a generic interval that combines days with either months or years as it does not make sense as you need a specific start date.
If the date spans years then you could edit your function and increase the leading precision of the INTERVAL to interval day(5) to second(0) which will let you store about 273 years and then your function will work for your sample data.
SELECT datediff( TIMESTAMP '1981-04-01 10:11:13', TIMESTAMP '2022-04-03 17:48:09')
as diff
FROM DUAL;
Outputs:
DIFF 14977 Days 7 Hours 36 Minutes 56 Seconds
Related Topics
How to Import a SQL Data File into SQL Server
SQL Select Rows with Only a Certain Value in Them
How to Get All Article Pages Under a Wikipedia Category and Its Sub-Categories
Anonymizing Customer Data for Development or Testing
Sql: How to Use Union and Order by a Specific Select
Doing a Where in on Multiple Columns in Postgresql
Resources Exceeded During Query Execution
How to Pass a Comma Separated List to a Stored Procedure
How to Short-Circuit SQL Where Clause
Remove Duplicates in a Django Query
Truncate All Tables in MySQL Database That Match a Name Pattern
Using Input from a Text File for Where Clause
How to Create a Conditional Where Clause
Xml Output Is Truncated in SQL