• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

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

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

  • 2
  • 2
2 Solutions
mysql> SELECT TIMEDIFF('2012-6-8 12:35','2012-6-8 7:34');
slightwv (䄆 Netminder) Commented:
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
not an oracle expert, but probably to_timestamp() is the way to go in oracle ...
slightwv (䄆 Netminder) Commented:
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.
neonagAuthor Commented:
Ok thanks slightwv
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now