SQL TRUNC Minutes from Sysdate
I am currently using one of the columns to extract every minute of the time, however, it would skip some minutes because no data was inserted that minute.
Output Below Expected.
12-19-2017 02:23:00
12-19-2017 02:24:00
12-19-2017 02:25:00
12-19-2017 02:26:00
12-19-2017 02:27:00
12-19-2017 02:28:00
12-19-2017 02:29:00
12-19-2017 02:30:00
12-19-2017 02:31:00
12-19-2017 02:32:00
12-19-2017 02:33:00
12-19-2017 02:34:00
What I am getting.(Current time is 13:11
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
12-19-2017 13:11:00
Query
SELECT TRUNC(SYSDATE,'MI') AS TIME_STAMP From Table where SYSDATE >= sysdate - 1;
Answer
if my starting time was from Yesterday midnight to now then I want to see output of each minute.
If you want to generate a range of values based on sysdate then you use a hierarchical query against the dual table, rather than having to refer to a real table:
select trunc(sysdate - 1) + (level - 1)/1440 as result
from dual
connect by level <= (sysdate - trunc(sysdate - 1))*1440
order by result;
RESULT
-------------------
2017-12-18 00:00:00
2017-12-18 00:01:00
2017-12-18 00:02:00
2017-12-18 00:03:00
...
2017-12-18 23:57:00
2017-12-18 23:58:00
2017-12-18 23:59:00
2017-12-19 00:00:00
2017-12-19 00:01:00
2017-12-19 00:02:00
2017-12-19 00:03:00
...
2017-12-19 14:21:00
2017-12-19 14:22:00
2017-12-19 14:23:00
This uses trunc(sysdate - 1) to get midnight yesterday as the starting point; calculates the number of minutes between then and now as (sysdate - trunc(sysdate - 1))*1440); and uses the connect-by syntax to add one minute to the start time until it reaches that limit.
(It’s up to, but not including, the current minute. If you want that as well, just add 1 to the end of the terminating condition.)
Depending on what you really want to end up with, you could use that as a CTE or inline view to generate all of those minutes, and then left-join to your real table to find matching data if it exists.
If you want a user to supply the start and end time you can pass those as variables, something like:
select trunc(:start_time, 'MI') + (level - 1)/1440 as result
from dual
connect by level <= (:end_time - :start_time)*1440
order by result;
where :start_time and :end_time here are bind variables that you populate with the start and end time. The exact mechanism depends on how the values will get from the user to the query; in SQL*Plus or a SQL Developer worksheet you'd need to get them as strings and add (explicit) conversion of those strings to dates. From a Java application say you can bind date datatypes directly.