CODEWITHSUNDEEP
sqloracle-databasedatetimetimestampsysdate
user206168
user206168

Reputation: 1025

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;

Upvotes: 0

Views: 24012

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

problem is : you're always selecting the same time (sysdate) from your table for all rows of today by 

SELECT trunc(sysdate,'mi')  as time_stamp
  FROM myTable 
 WHERE sysdate >= sysdate - 1;

try to rewrite the query as :

SELECT to_char(date1,'mm-dd-yyyy hh:mi:ss')  as time_stamp
  FROM myTable 
 WHERE sysdate >= sysdate - 1; 
-- assuming you have a date column named DATE1 in your table, and populated every minute.

(comparing hour 02 and 13, i decided to format with hh instead of hh24)

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 5

Related Questions