create a table

I need to create a minute table similar to the one given below in ORACLE DBMS. I am finding it hard to do it in ORACLE . It throws some or the other kind of error everytime.

           create table Minutes([Minute] varchar(5)) 
           declare @ToDay datetime = '2018-01-11 00:00:00' 
           --loop to fill minutes table with all minutes in a day
           cast(@ToDay as Date) <'2018-01-12'
           insert into Minutes(Minute) select left(cast( @ToDay as Time),5)
           set @ToDay =  DATEADD(MINUTE,1, @ToDay)
more ▼

asked Jan 11 at 07:59 AM in Default

avatar image

31 1 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can do many things in Oracle PL-SQL, but you cannot use the SQL Server specific T-SQL. During recent years the syntax differences between these flavours of SQL have become smaller, but still, there are some differences. For example, never use the VARCHAR data type in Oracle, it has been pretty much deprecated for last 40 years or so, use VARCHAR2 instead. DATEADD function exists, but the parameters are different, and there is no way to add minutes, some other function must be used for that.

From the question definition it looks like all you need is the table with one column named Minute and 1,440 rows storing values from 00:00 to 23:59. If this assumption is correct then query below should work. I don't have access to any Oracle environment at this time so my query is written blindly with no possibility to check whether there are any syntax errors or not. I hope that it works as written. The idea is to generate numbers 0 through 23 (hours), numbers 0 through 59 (minutes) and then cross join the 2 to come up with all their combinations (formatted as hh:mm). Here is the script which I hope works as is:

     lpad(hr.h, 2, '0')||':'||lpad(mnt.m, 2, '0') "Minute" 
     from (
         select rownum - 1 h from dual connect by rownum <= 24
     ) hr,
         select rownum - 1 m from dual connect by rownum <= 60    
     ) mnt

If this works but the order of rows is incorrect then add the order by h, m to the query.

Hope this helps.


more ▼

answered Jan 11 at 02:25 PM

avatar image

20.6k 3 7 29

@Oleg : Thanks for the update....I'm working on it.

Jan 12 at 08:04 AM blackwidow

I am not an expert in oracle but I do have the luxury of verifying the syntax in Oracle and @Oleg's query was correct as usual. :)

Another way of achieving the same could be

 select to_char(to_date(SYSDATE)  + numToDSInterval(rownum - 1, 'minute'), 'hh24:mi')
 from (select rownum from dual connect by rownum <= 1440)h;

With TO_CHAR you can get any format you want.

@Oleg Actually, the whole point was to say "Hi" to you. :) It has been a long time and good to see you helping out others continuously. Hats off to you.

Jan 12 at 02:08 PM Usman Butt

@Usman Butt Thank you! Actually, I am guilty of being not active 2014 - 2016. During that time I worked at the place which I call easily call absolutely the best place I ever worked (or will ever work in the future), but there was so much work and so many tight drop dead dates that I did not have any time to visit this site. I will try my best to stay active at this time. It is good to see you back!

By the way, this is a very neat script you posted in the comments, looks better than the script in my answer because it uses the single select of 1440 rows (courtesy of numToDSInterval) rather than marriage of the 60 and 24 like I have in my script.

Jan 16 at 03:55 PM Oleg

@blackwidow Yes, the way it is written is guaranteed to run forever due to that OR part which is not "linked" to other AND conditions, and therefore, produces many more rows than needed and most of them are needlessly repeated/recounted. In a mean time, it looks like you did not want to use any of the offered solutions to the actual question (create a table with 1440 rows and values in hh:mm format, one row per minute of the day). There was a script in my answer and in @Usman Butt comment, both scripts produces exactly what you have asked. It would be nice though if you let us know whether the scripts work/help with solving original problem in question or not (even though we know they do).

What you are asking in your comment is a different question but I will have a look later today and add it to my answer. Thank you.

Jan 17 at 01:51 PM Oleg

@blackwidow I totally agree with @Oleg, you should have asked this as a separate question. Please post the DDL of your table, some sample data and the required output in that question. Help us to help you.

Moreover, since you got at least the basic idea to solve your original problem from @Oleg's answer, please mark it as the answer if possible.

Jan 17 at 02:16 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

As far as your new query is concerned, as already pointed out by @Oleg there seems to be some logical problems with your query, you would have to change it something like

 with minutes (minute) as (
        select cast(trunc(sysdate-1) as timestamp) + interval '1' minute * (level - 1)
        from dual
        connect by level <= 1440
        select to_char(m.minute, 'MM/DD/YYYY HH24:MI') as minute, count(o.order_id) as 
        from minutes m
        left join ord_order o
       on odcreate >= cast(trunc(sysdate-1) as timestamp)
       and odcreate < cast(trunc(sysdate) as timestamp) --it is redundant to sysdate -1  add interval 1 day
       and odcomplete is not null
       and odcomplete >= m.minute and odcomplete < m.minute + interval '1' minute
       and (to_char(odcomplete, 'hh24:MI') >= to_char(odcreate, 'hh24:MI')) --implement as a constraint
       group by m.minute
       order by m.minute;

I believe complete date should always be greater than the created date, and this should have been implemented as a constraint.

But this all is a guess work, and if you need better help, then post a new question with all the details. Cheers.

more ▼

answered Jan 17 at 02:23 PM

avatar image

Usman Butt
14.9k 6 13 21

I wanted to add this answer as a comment but not able to do so :( Even "convert to comment" not working.

Jan 17 at 02:24 PM Usman Butt

@blackwidow It really looks like you need to either post a new question or let us know whether there is another question which you already asked in the past. In your question, please describe the problem, and provide a small set of sample data and desired output. This way it will be easier to figure out what needs to happen. Thank you.

Jan 17 at 02:55 PM Oleg

@blackwidow Your statements are a bit confusing. This is why I asked you to post the DDL of the table, some data and the required output in a separate question. If we can help you on this thread, we will help you on the new thread as well. This to and fro communication will not help any of us. So please help us to help you.

Jan 17 at 02:58 PM Usman Butt

@Usman Butt @Oleg : I am posting the question along with the requirements.

Jan 17 at 03:29 PM blackwidow
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 11 at 07:59 AM

Seen: 94 times

Last Updated: Jan 17 at 03:29 PM

Copyright 2018 Redgate Software. Privacy Policy