# Thread: Diff between TIME and TIMESTAMP

1. Registered User
Join Date
May 2008
Posts
35

## Unanswered: Diff between TIME and TIMESTAMP

I need to make a difference between two columns of TIME and TIMESTAMP
the conversion never works.

I have date time stored in timestamp and time stored in TIME column and i need to find a difference between the timings.

2. Registered User
Join Date
Dec 2005
Posts
273
what SQL did you try and what error did you receive ?

3. Registered User
Join Date
May 2008
Posts
35
select timestampdiff (4,char(TIMESTAMP(COLA) - COLB)) AS ELAPSEDTIME from TABLEA

Unexpected token timestamp found

4. Registered User
Join Date
Dec 2005
Posts
273
I guess COLA is of type TIME and COLB is of type TIMESTAMP ?

if I'm right, try:

SELECT
TIMESTAMPDIFF ( 4, CHAR(TIMESTAMP( DATE(COLB),COLA)- COLB)) as ELAPSEDTIME
FROM tablea
Last edited by umayer; 11-30-09 at 08:17.

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
You can't compare TIME and TIMESTAMP directly, as you know.

So, conversion of data type is necessary to compare or to subtract.
One way is to convert TIME to TIMESTAMP, another is to convert TIMESTAMP to TIME.

Each have a problem.
1) Convert TIME to TIMESTAMP.
TIMESTAMP includes date. So, assuming date for the TIME is necessary. How will you do?

2) Convert TIMESTAMP to TIME.
The date part of TIMESTAMP is neglected. Is it no problem?

Here are examples(cola is TIME, colb is TIMESTAMP):
(not tested)
1) Convert TIME to TIMESTAMP.
Assuming same date of TIMESTAMP:
TIMESTAMPDIFF(4, CHAR(TIMESTAMP(DATE(colb), cola) - colb))
You will get the difference by minutes.

2) Convert TIMESTAMP to TIME.
The date part of TIMESTAMP is neglected.
( MIDNIGHT_SECONDS(cola) - MIDNIGHT_SECONDS(TIME(colb)) ) / 60

6. Registered User
Join Date
May 2008
Posts
35
thanks it worked

7. Registered User
Join Date
May 2008
Posts
35
Can the difference be found out in hours , say if difference is 1 hour 2 min and 3 sec then 01:00:03

8. Registered User
Join Date
Dec 2005
Posts
273
If you subtract to times, you receive a time duration. That is a decimal number containing HHMMSS.

eg:

SELECT TIME('14:03:17') - TIME('04:12:19')

returns 95058 , that means the difference is 9 hours, 50 minutes and 58 seconds

9. Registered User
Join Date
May 2008
Posts
35
I need it to work on existing query

The date part of TIMESTAMP is neglected.
( MIDNIGHT_SECONDS(cola) - MIDNIGHT_SECONDS(TIME(colb)) ) / 60

This gives in minutes.

10. Registered User
Join Date
Dec 2005
Posts
273

SELECT cola - time(colb) ...

11. Registered User
Join Date
May 2008
Posts
35
Tried this
(MIDNIGHT_SECONDS(TIME(COLB)) - MIDNIGHT_SECONDS(COLA) )

Gives 002744 for 27Mins 44 Seconds can this be formated to say 00:27:44

12. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Why didn't you try umayer's suggestion?

SELECT cola - time(colb)

13. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Tried this
(MIDNIGHT_SECONDS(TIME(COLB)) - MIDNIGHT_SECONDS(COLA) )

Gives 002744 for 27Mins 44 Seconds can this be formated to say 00:27:44
If (MIDNIGHT_SECONDS(TIME(COLB)) - MIDNIGHT_SECONDS(COLA) ) gives 002744, it is 2744 Seconds(not 27Mins 44 Seconds).

See this example:
Code:
```------------------------------ Commands Entered ------------------------------
SELECT cola, colb
, (MIDNIGHT_SECONDS(TIME(COLB)) - MIDNIGHT_SECONDS(COLA) ) AS midnight_seconds_diff
, time(colb) - cola AS time_diff
, TRANSLATE('ab:cd:ef', DIGITS(time(colb) - cola), 'abcdef') format_time_diff
FROM (VALUES (TIME('10:00:00'), TIMESTAMP('2009-12-02-10.45.44') ) ) t(cola, colb);
------------------------------------------------------------------------------

COLA     COLB                       MIDNIGHT_SECONDS_DIFF TIME_DIFF FORMAT_TIME_DIFF
-------- -------------------------- --------------------- --------- ----------------
10:00:00 2009-12-02-10.45.44.000000                  2744     4544. 00:45:44

1 record(s) selected.```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•