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

Posted on 2012-08-15
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
    LVL 51

    Assisted Solution

    mysql> SELECT TIMEDIFF('2012-6-8 12:35','2012-6-8 7:34');
    LVL 76

    Accepted Solution

    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

    not an oracle expert, but probably to_timestamp() is the way to go in oracle ...
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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

    Ok thanks slightwv

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Let's say you need to move the data of a file system from one partition to another. This generally involves dismounting the file system, backing it up to tapes, and restoring it to a new partition. You may also copy the file system from one place to…
    Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
    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 find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now