Oracle Date / Timestamp difference in Hours, Minutes & Seconds

Figuring out the exact time difference that has elapsed between between two Date / Timestamp columns in Oracle using SQL is not as easy as it sounds.
A simple subtraction the those values using this query yields:

SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0 from run_log;

 RUN STARTTIME            ENDTIME              TOTALMINUTES0 
---- -------------------- -------------------- ------------- 
   3 24-APR-15 12:07:49   24-APR-15 12:58:37   0.03527777778  
   2 24-APR-15 11:00:47   24-APR-15 11:55:11   0.03777777778  
   1 24-APR-15 08:00:13   24-APR-15 08:52:39   0.03641203704  

So, I convert the difference to minutes by multiplying it by 1440 (24 Hours X 60 Minutes) using this below query:

SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0, 
        to_char((endtime-starttime)*1440,'99.99') TotalMinutes1 -- OR (24 Hours X 60 Minutes)
     from run_log order by starttime desc ;

 RUN STARTTIME            ENDTIME              TOTALMINUTES0 TOTALMINUTES1 
---- -------------------- -------------------- ------------- ------------- 
   3 24-APR-15 12:07:49   24-APR-15 12:58:37   0.03527777778  50.80         
   2 24-APR-15 11:00:47   24-APR-15 11:55:11   0.03777777778  54.40         
   1 24-APR-15 08:00:13   24-APR-15 08:52:39   0.03641203704  52.43         

That's great progress, but now the seconds part is a decimal fraction instead of seconds. This necessitates the wielding of the Oracle "numtodsinterval" built-in function using this below query:

SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
         to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
         numtodsinterval((endtime-starttime),'day') TotalMinutes2
     from run_log order by starttime desc ;

 RUN STARTTIME            ENDTIME              TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 
---- -------------------- -------------------- ------------- ------------- ------------- 
   3 24-APR-15 12:07:49   24-APR-15 12:58:37   0.03527777778  50.80        0 0:50:48.0   
   2 24-APR-15 11:00:47   24-APR-15 11:55:11   0.03777777778  54.40        0 0:54:24.0   
   1 24-APR-15 08:00:13   24-APR-15 08:52:39   0.03641203704  52.43        0 0:52:26.0   

The output is good and we now only need to trim it a bit as in the below query:

SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
         to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
         numtodsinterval((endtime-starttime),'day') TotalMinutes2,
         substr(numtodsinterval((endtime-starttime),'day'), 4, 8) TotalMinutes3 -- Ignore hours
     from run_log order by starttime desc ;

 RUN STARTTIME            ENDTIME              TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 TOTALMINUTES3 
---- -------------------- -------------------- ------------- ------------- ------------- ------------- 
   3 24-APR-15 12:07:49   24-APR-15 12:58:37   0.03527777778  50.80        0 0:50:48.0   0000000       
   2 24-APR-15 11:00:47   24-APR-15 11:55:11   0.03777777778  54.40        0 0:54:24.0   0000000       
   1 24-APR-15 08:00:13   24-APR-15 08:52:39   0.03641203704  52.43        0 0:52:26.0   0000000       

Now that's really very strange - the trimming substring does not get me the expected value - wonder what's going on? To check that, we use "to_char" function to see what is happening during the conversion to string as in the below query:

SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
         to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
         numtodsinterval((endtime-starttime),'day') TotalMinutes2,
         substr(numtodsinterval((endtime-starttime),'day'), 4, 8) TotalMinutes3, -- Ignore hours
         to_char(numtodsinterval((endtime-starttime),'day')) TotalMinutes4
     from run_log order by starttime desc ;

 RUN STARTTIME            ENDTIME              TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 TOTALMINUTES3 TOTALMINUTES4                  
---- -------------------- -------------------- ------------- ------------- ------------- ------------- ------------------------------ 
   3 24-APR-15 12:07:49   24-APR-15 12:58:37   0.03527777778  50.80        0 0:50:48.0   0000000       +000000000 00:50:48.000000000  
   2 24-APR-15 11:00:47   24-APR-15 11:55:11   0.03777777778  54.40        0 0:54:24.0   0000000       +000000000 00:54:24.000000000  
   1 24-APR-15 08:00:13   24-APR-15 08:52:39   0.03641203704  52.43        0 0:52:26.0   0000000       +000000000 00:52:26.000000000  

Ahh ha! Substring converts the given value to string before getting us a part of it and the conversion to string gives us a totally different & larger string! So now we use trim the result as in the below query to get the executed final result:

SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
         to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
         numtodsinterval((endtime-starttime),'day') TotalMinutes2,
         substr(numtodsinterval((endtime-starttime),'day'), 4, 8) TotalMinutes3, -- Ignore hours
         to_char(numtodsinterval((endtime-starttime),'day')) TotalMinutes4,
         substr(numtodsinterval((endtime-starttime),'day'), 15, 8) TotalMinutes5 -- Ignore hours
     from run_log order by starttime desc ;

 RUN STARTTIME            ENDTIME              TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 TOTALMINUTES3 TOTALMINUTES4                  TOTALMINUTES5
---- -------------------- -------------------- ------------- ------------- ------------- ------------- ------------------------------ -------------
   3 24-APR-15 12:07:49   24-APR-15 12:58:37   0.03527777778  50.80        0 0:50:48.0   0000000       +000000000 00:50:48.000000000  50:48.00      
   2 24-APR-15 11:00:47   24-APR-15 11:55:11   0.03777777778  54.40        0 0:54:24.0   0000000       +000000000 00:54:24.000000000  54:24.00      
   1 24-APR-15 08:00:13   24-APR-15 08:52:39   0.03641203704  52.43        0 0:52:26.0   0000000       +000000000 00:52:26.000000000  52:26.00      

Finally! The time difference is displayed in the format HH24:MI:SS.NNN

So after five iterations, I finalize that this exercise requires using the Oracle built-in function "numtodsinterval" which takes a number of a given interval unit and converts it to an INTERVAL DAY TO SECOND literal. The interval value indicates the unit of the specified number and the case-insensitive valid values are:

• 'DAY'
• 'HOUR'
• 'MINUTE'
• 'SECOND'

In our case, the number in question is the simple difference of the two time values and its' unit is a day so the interval value is "Day".

Date Math in Oracle

If you are using Oracle 9i or greater, you can use the interval data type to do simple Date arithmetic as follows:

select sysdate + interval '2' hour from dual; -- to add 2 hours to current time

select sysdate + interval '5' minute from dual; -- to add 5 minutes to current time

select sysdate + interval '24' second from dual; -- to add 24 seconds to current time

For adding or subtracting days, you can simply add it as below:

select sysdate + 3 from dual; -- to add 3 days to current date

select sysdate - 6 from dual; -- to subtract 6 days to current date

select last_day(sysdate) - sysdate from dual; -- to find the number of days from current date to end of month

However when adding months, it is better to use the functions provided by Oracle:

select add_months(sysdate, 2) from dual; -- to add 2 months to current date

select add_months(sysdate, -3) from dual; -- to subtract 3 months to current date

To select the date of the next specified day, you can use the NEXT_DAY function:

select next_day(sysdate, 'SAT') from dual; -- to get the date on next Saturday after current date
-- use SUN, MON, TUE, WED, THU, FRI or SAT

To get the last date of the month for the specified day, you can use the LAST_DAY function:

select last_day(sysdate) from dual; -- to get the date on last day of current month

Count occurance of character in a string

To count the number of times a particular character occurs in a string, use the below SQL query.
If you are using Oracle 11, you can use the new REGEXP_COUNT - note that it uses regular expressions and so special character need to be escaped:

SELECT  REGEXP_COUNT (first_name, '\.') 
AS dot_count 
FROM employee;

If you are using any other version of Oracle, then use this query:

SELECT  first_name, 
LENGTH (first_name) - LENGTH (REPLACE (first_name, '.')) 
AS dot_count 
FROM employee;

Change format of Date displayed in SQL Developer

To change the format of Date column displayed in SQL Developer worksheets (output of SQL queries) do the following:

1. Select Tools > Preferences
2. Expand the Database section
3. In the NLS Date format field, enter the desired date format as supported by Oracle

My preferred format is DD-MON-RR HH24:MI:SS

Restore MS SQL Server Backup file

Recently I was faced with a dilemma - a friend of mine had a M$ SQL Server backup file and wanted me to extract some data from it. Since I work on Ubuntu & LAMP stack, I had to grab hold of a Win7 laptop and then follow the below steps.

To restore a database you need to do three things:

1. Install MS SQL Server Express
2. Interrogate the backup file to find the logical file names it contains
3. Restore the file into the appropriate database

Assume: For this example, the backup file is C:\Backups\DB_Backup_20110131.bak

Step 1: Install MS SQL Server
Go to Micro$oft site using the direct link http://www.microsoft.com/express/Database/I-nstallOptions.aspx and download and install the most appropriate version of SQL Server Express.

Step 2: Interrogate Backup File

RESTORE FILELISTONLY FROM DISK = 'C:\Backups\DB_Backup_20110131.bak'

This will return something like the below set of rows which represent the internal logical composition of the backup file:

LogicalName        PhysicalName                 Type FileGroupName Size      MaxSize
------------------ ---------------------------- ---- ------------- --------- --------------
SourceDatabase_data C:\SqlServer\Src_DB_Data.mdf D    PRIMARY       836461765 35184372080640
SourceDatabase_log  C:\SqlServer\Src_DB_Log.ldf  L    NULL           91592723 35184372080640

Step 3: Restore from Backup File

RESTORE DATABASE Destination_DB
FROM DISK = 'C:\Backups\DB_Backup_20110131.bak'
WITH 
     REPLACE, -- Overwrite DB - if one exists
     RECOVERY, -- Use if this is the only file to recover
     STATS = 10, -- Show progress (every 10%)
     MOVE 'SourceDatabase_data' TO 'C:\SqlServer\Src_DB_Data.mdf', 
     MOVE 'SourceDatabase_log' TO 'C:\SqlServer\Src_DB_Data.mdf'

11 Free Productivity Tools for the office

Here's a list of 11 Free Tools to increase productivity at Work:

1. 7-Zip:
   Category: Compression/Decompression
   Details: Compression utility that can handle almost all possible formats
   URL: http://www.7-zip.org
   
2. Eclipse:
   Category: IDE
   Details: Very popular IDE that supports a vast array of languages and configurations
   URL: http://www.eclipse.org
   
3. FileZilla:
   Category: FTP Client (& Server)
   Details: FTP Client and server with lots of options
   URL: http://filezilla-project.org
   
4. PuTTY:
   Category: Telnet/SSH
   Details: Very simple but powerful Telnet/SSH client
   URL: http://www.chiark.greenend.org.uk/~sgtatham/putty/
   
5. PuTTy Connection Manager:
   Category: Telnet/SSH
   Details: Allows docking of multiple PuTTY session windows as Tabs and allows user to create a database of connections that includes user names, passwords, script to run after login, etc.
   URL: http://puttycm.free.fr
   
6. Notepad++:
   Category: Editor
   Details: Very simple, but powerful and full featured editor that supports syntax highlighting, plug-ins, macros, regexes, etc.
   URL: http://notepad-plus-plus.org
   
7. OpenOffice:
   Category: Office Software
   Details: Full featured replacement for the cos$tly M$ Office Suite of products. Includes software for word processing, spreadsheets, presentations, graphics, databases and more.
   URL: http://www.openoffice.org
   
8. PDFCreator:
   Category: Office Software
   Details: Create PDF files from any application.
   URL: http://www.pdfforge.org/pdfcreator/
   
9. VirtualDimension:
   Category: Desktop Manager
   Details: Fast and full-featured virtual desktop manager that allows you to create virtual desktops on Windows just like X-Win.
   URL: http://virt-dimension.sourceforge.net
   
10. VistaSwitcher:
    Category: Task Manager
    Details: Replaces the default Windows Alt-Tab dialog with a nice box that shows a list of all running tasks, with their names and preview snapshots, and enables the user to take actions on them using the mouse and keyboard shortcuts.
    URL: http://www.ntwind.com/software/vistaswitcher/
    
11. WinMerge:
    Category: Development
    Details: Visual diff and merge tool for Windows that can compare both folders and files
    URL: http://winmerge.org
    

Other good free softwares that I also considered were:

1. PSPad:
   Category: Editor
   Details: Another powerful and full featured editor that supports syntax highlighting, plug-ins, macros, regexes, etc.
   URL: http://www.pspad.com
   
2. PDFReDirect:
   Category: Office Software
   Details: Another simple software to create PDF files.
   URL: http://www.exp-systems.com
   
3. DiffMerge:
   Category: Development
   Details: Visual diff and merge tool for Windows, Mac OS X and Linux that can compare both folders and files
   URL: http://www.sourcegear.com/diffmerge/
   
4. Toad Free:
   Category: Database Access
   Details: Free version of the powerful Toad for Oracle with a few features disabled.
   URL: http://www.toadworld.com/Freeware/tabid/680/Default.aspx
   
5. SQLDeveloper for Oracle:
   Category: Database Access
   Details: Oracle client provided by Oracle
   URL: http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
   

Please let me know if you see other good free software worthy of including in the above list!

Checking for NaNs

In Sybase 12.5.4, checking for NaN is very simple - just convert the number to a string and check for the string!

select * from mydata where STR(numcol) like '%NaN%'

From Oracle 10g onwards, this is very simple as a special NaN value is made available for checking against:

select * from mydata where numcol is NaN

Deleting duplicate rows in Sybase

I had to delete a set of duplicate rows in Sybase and that had me in a quandry. I had done that many-a-times in Oracle using the ROWID psuedo-column but nothing similar is available in Sybase or for that matter in SQL Server. Here's how to go about this daunting task in Sybase!


The deletion of duplicate rows cannot be done in a single SQL query like it can be done in Oracle. Hence it needs to be done in 3 steps:

1. Create temporary table with one instance of each distinct row
2. Delete all rows having multiple instances
3. Move back the unique data from temporary table to actual table

Step 1:
Create temporary table with one instance of each distinct row with the below query.

select distinct t.* into temp_table_name
from data_table_name t
group by keycol1, keycol2, keycol3 
having count(0) > 1

Note: that this step creates the temp_table_name table so it will fail if a table by that name already exists.

This step puts one instance of each distinct row into the temporary table. However, some of the rows may contain identical values in the key columns but different data in the remaining columns. At this point you need to check the data in the temp_table_name and reconcile which of the rows you wish to keep for a given duplicate key value. You may even need to do these three steps on the temporary table to achieve a set of unique records in the temporary table.

Step 2:
Delete all rows having multiple instances with the below query.

delete data_table_name from temp_table_name 
where data_table_name.keycol1 = temp_table_name.keycol1 
and data_table_name.keycol2 = temp_table_name.keycol2 
and data_table_name.keycol3 = temp_table_name.keycol3

Step 3:
Move back the unique data from temporary table to actual table with the below query.

insert into data_table_name 
select * from temp_table_name

Remember to change the table & column names in the above queries.
Replace data_table_name with your actual table name,
temp_table_name with your choice of name for temporary table and
keycol1..n with the actual table column names!

Check out my previous post here for doing this same thing in Oracle.

Deleting duplicate rows in Oracle

Deleting duplicate rows in Oracle seems to be the simplest to me (may be because I have used it more or may be its quite simple really!). Lets get down straight to the code and do a recap of how to delete duplicate rows in Oracle.

Simple Solution
Note that this simple solution assumes that there are no primary or unique keys on the table but the combination of data in a set of columns defines the duplicates. The example shows three key columns, but you can use more or less columns to identify unique rows based on your actual data.

DELETE FROM data_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM data_table
GROUP BY keycol1, keycol2, keycol3

However, there could be cases where the table has a sequence / auto-generated column as the primary key. In such cases too, you can use the above query after substituting rowid with the id column name.

Advanced Solution
If you are looking for an advanced and faster solution with more features & flexibility, you can use the following solution that I found on devx. This solution uses RANK() function's capabilities to provide the extra features.

DELETE
FROM data_table
WHERE ROWID IN
(SELECT tmp_key
FROM
(SELECT tmp_key, keycol1, keycol2,
RANK() OVER (PARTITION BY keycol1, keycol2 ORDER BY tmp_key) AS seq_num
FROM
(SELECT rowid as tmp_key, keycol1, keycol2
FROM data_table
WHERE (keycol1, keycol2) IN
(SELECT keycol1, keycol2
FROM data_table
GROUP BY keycol1, keycol2
HAVING COUNT(0) > 1)))
WHERE seq_num > 1)

Let me break up this complicated query into parts and explain in detail.

1. The innermost sub-query (lines 12 - 15) finds those records that have duplicates. This gives us the sub-set of data that are only duplicate rows.
2. The next outer sub-query (lines 9 - 11) uses ROWD psuedo-column to create a single key column. This adds a key column to the set of data rows we are interested in.
3. The next outer sub-query (lines 6 - 8) uses RANK() function to dynamically assign sequence numbers to rows in the set of duplicate rows and orders them. Note that there are a set of sequence numbers each for each set of duplicate rows. This assigns a sequence number to and orders the rows inside each set of duplicates.
4. The next outer sub-query (lines 4, 5 & 16) selects the duplicate rows based on the sequence number assigned by the RANK function is above step.
5. The outer-most query (lines 1 - 3) deletes the duplicate rows.

Note: In case the table has a unique / primary key, substitute that instead of the ROWID psuedo-column in the above solution to get the same results.

Now, the extra features that using RANK provides are that you can choose the number of rows you want to keep. For example, you want to keep only the top 10 rows, you can change the numbers in lines 15 & 16 of the above query from 1 to 10 and add a proper ORDER BY to line 7 to get the data sorted in the required manner to determine the "top 10 rows".

Similarly, if you want to keep only the latest row, you can add a proper ORDER BY clause to line 7 to get the data sorted in the required manner to determine the latest row.

Remember to change the table & column names in the above queries.
Replace data_table with your actual table name and
keycol1..n with the actual table column names!

Check out my next post here for doing this same thing in Sybase.

SQuirreL behaving weird on Ubuntu

I needed to access MySQL & MS SQL databases from my Ubuntu 9.04 laptop and was at a total loss. After a bit of research, SQuirreL sql client looked like the best choice.
However, since this is not available through the Synaptics Package Manager so it had to be downloaded and installed manually - which is the way software used to be installed on *nix boxes originally.

At that time, SQuirreL 3.0.2 was the latest version and all versions starting from 3.0 require Java6 - this I was able to install from the Synaptics Package Manager. Installing SQuirreL was simple: Run the command sudo java -jar squirrel-sql-3.0.2-install.jar and it was installed.

However I was unable to access MS SQL Server DB yet as I did not have the proper JDBC drivers for that. After narrowing down my choices, I found I was left with jTDS which is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005 and 2008) and Sybase (10, 11, 12, 15). Just put the jtds jar file in the squirrel install lib folder and then add the new driver from the UI.

After this, I was able to access MS SQL server but often, the program would just freeze or become unresponsive after a few minutes of use (or lack thereof). I would need to kill the java process and restart it. Also, the automatic updates never worked and so the software remained the same version till I decided to take things into my hands.

At this time the latest version available was 3.1 which was good news indeed - I was expecting a full solution to my problems! However, the website offered no documentation on how to upgrade the software, only how to install it. So I went ahead and installed it in a similar fashion - but this time it installed in a different folder. The previous install was in the folder /usr/local/SQuirrel SQL Client/ and the new one was in the folder /usr/local/squirrel-sql-3.1/

I then tried to uninstall the older version by running the uninstall.jar from the Unintall folder of the previous install. However that did not do much and so I just removed that whole folder - just like we used to remove software from *nix in the "good old days"! The good news was that the configuration was stored in my home folder in the .squirrel-sql folder so all aliases/connections/drivers etc were intact and I was able to use them immediately.

But alas! Even after all this effort, the problem of SQuirreL freezing after a few minutes of use still did not go away!

Please let me know if you see any solution to this problem or if you come across a better SQL Client for Ubuntu which can access MySQL & MS SQL!

MariaDB drop-in replacement for MySQL

Since Oracle is in the process of buying out Sun & hence MySQL, the creator of MySQL Michael "Monty" Widenius went on to create MariaDB which is a drop-in replacement for MySQL.

Seems that they are working on lots of improvements and cleanup of the legacy code that had crept in. They have a better storage engine to replace MyISAM, speed improvements, more extensions, etc. Check out the differences here.

Some interesting reading on MariaDB & MySQL can be found on Monty's blog.

One of the very interesting things I stumbled across on the Monty Program website was their business model titled "The hacking business model". This is the model on which their company runs. It is very transparent and employee friendly model which works towards a win-win situation for the company as well as the employee. Read more about it here. This is a very good business model for companies that work on OSS - wish my company was based on this model ;)