Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Subtraction of date and time in SQL to get the number of hours/minutes taken

Posted on 2012-08-15
Medium Priority
Last Modified: 2012-08-16
I finally loaded the session log which is in CSV format into a database table called TAB1
with columns

How do you subtract timestamps or dates?

SELECT timestamp FROM TAB1 WHERE MESSAGE_CODE = 'WRT_8006' minus
SELECT timestamp FROM TAB1 WHERE message_code = 'WRT_8147'

SELECT timestamp FROM TAB1 WHERE MESSAGE_CODE = 'WRT_8006'  :--> 8/6/2012 12:35
SELECT timestamp FROM TAB1 WHERE message_code = 'WRT_8147'----->8/6/2012 7:34

i would expect 8/6/2012 12:35 minus 8/6/2012 7:34   ...around 5 hrs

but i am unable to do
this timestamp is stored as VARCHAR2 datatype in TAB1

so any inputs  of how to do  to_DATE (timestamp) and do subtraction

or tell me if any-other method  to calculate the timestamp difference so i can figure out how long it writer thread took pl;ace

Question by:neonag
  • 2
  • 2
LVL 51

Assisted Solution

ahoffmann earned 1000 total points
ID: 38299819
mysql> SELECT TIMEDIFF('2012-6-8 12:35','2012-6-8 7:34');
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1000 total points
ID: 38300152
This was posted in an Unix OS zone.  What database are you using?

The tag mentions Oracle but I wanted to make sure.

If Oracle and the carchar2 has the format: 8/6/2012 7:34

subtracting dates in Oracle produces the number of days.  Then it is simple math to get any variation from that.  

To get hours:
select (to_date('8/6/2012 12:35','MM/DD/YYYY HH24:MI') - to_date('8/6/2012 7:34','MM/DD/YYYY HH24:MI'))*24 from dual
LVL 51

Expert Comment

ID: 38300305
not an oracle expert, but probably to_timestamp() is the way to go in oracle ...
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38300818
I would have liked you to have clarified the database so we could have gotten this question into the correct zones.

It was just luck that I received the Neglected Question alert in Unix.

You would have gotten the correct response much faster had it been asked in the proper zone.

Author Comment

ID: 38300862
Ok thanks slightwv

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello fellow BSD lovers, I've created a patch process for patching openjdk6 for BSD (FreeBSD specifically), although I tried to keep all BSD versions in mind when creating my patch. Welcome to OpenJDK6 on BSD First let me start with a little …
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question