Results 1 to 13 of 13
  1. #1
    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. #2
    Join Date
    Dec 2005
    Posts
    273
    what SQL did you try and what error did you receive ?

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


    Unexpected token timestamp found

  4. #4
    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 09:17.

  5. #5
    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. #6
    Join Date
    May 2008
    Posts
    35
    thanks it worked

  7. #7
    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. #8
    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. #9
    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. #10
    Join Date
    Dec 2005
    Posts
    273
    how about:

    SELECT cola - time(colb) ...

  11. #11
    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. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you try umayer's suggestion?
    how about:

    SELECT cola - time(colb)

  13. #13
    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
  •