| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-30-09, 05:40
|
|
Registered User
|
|
Join Date: May 2008
Posts: 34
|
|
|
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.
|
|
11-30-09, 05:55
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 273
|
|
what SQL did you try and what error did you receive ?
|
|
11-30-09, 06:19
|
|
Registered User
|
|
Join Date: May 2008
Posts: 34
|
|
|
|
select timestampdiff (4,char(TIMESTAMP(COLA) - COLB)) AS ELAPSEDTIME from TABLEA
Unexpected token timestamp found
|
|
11-30-09, 08:13
|
|
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.
|
11-30-09, 08:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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
|
|
12-01-09, 01:22
|
|
Registered User
|
|
Join Date: May 2008
Posts: 34
|
|
|
|
12-01-09, 23:14
|
|
Registered User
|
|
Join Date: May 2008
Posts: 34
|
|
Can the difference be found out in hours , say if difference is 1 hour 2 min and 3 sec then 01:00:03
|
|
12-02-09, 02:20
|
|
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
|
|
12-02-09, 03:18
|
|
Registered User
|
|
Join Date: May 2008
Posts: 34
|
|
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.
|
|
12-02-09, 05:03
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 273
|
|
how about:
SELECT cola - time(colb) ...
|
|
12-02-09, 06:12
|
|
Registered User
|
|
Join Date: May 2008
Posts: 34
|
|
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-02-09, 06:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Why didn't you try umayer's suggestion?
Quote:
how about:
SELECT cola - time(colb)
|
|
|
12-02-09, 07:27
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Diff between TIME and TIMESTAMP
