Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Computing the difference between two time values by means of SQL

  1. #1

    Question Computing the difference between two time values by means of SQL

    Two columns of a table (for the database engine “HyperSQL” within the application “LibreOffice Base 7.1.0.3-985.1”) have got the data type “TIME”.
    I would like to calculate a difference for these fields.

    It seems that the following SQL statement is insufficient so far.

    SELECT ID, t1, t2, t2 - t1 AS duration FROM time_measurements_test


    I would appreciate your advices.

    Would you like to point further information sources out besides query examples which work with dates and time stamps?

  2. #2
    Join Date
    Jun 2008
    Location
    Netherlands
    Posts
    28,285

    Default Re: Computing the difference between two time values by means of SQL

    I have no idea what the format is of the type TIME.

    In Unix (and thus Linux) time stamps are in milliseconds since the Epoch (1970-01-01 00:00:00.000, which is thus time stamp 0).
    Easy to calcultate the difference in milliseconds between two.

    But when your's is some character format, it is much less easier.
    Henk van Velden

  3. #3
    Join Date
    Feb 2010
    Location
    Germany
    Posts
    3,967

    Default Re: Computing the difference between two time values by means of SQL

    @elfring:

    Maybe, take a look at the HyperSQL “Interval Types” – <https://hsqldb.org/doc/guide/sqlgene..._interval_typs> …

  4. #4
    Join Date
    Jun 2008
    Location
    West Yorkshire, UK
    Posts
    3,571

    Default Re: Computing the difference between two time values by means of SQL

    The format of a TIME column is (was when I last checked) HH:MM:SS with microsecond precision; -838:59:59.999999 to 838:59:59.999999; with the delimiters, they are treated as time of day; without the delimiters as elapsed time.

    ‘duration’ is a temporary column and must not appear in any table in use; ‘time_measurements_test’ is presumably the table which holds t1 and t2; I assume you have terminated the expression with ;

  5. #5

    Question Re: Computing the difference between two time values by means of SQL

    Quote Originally Posted by john_hudson View Post
    ‘duration’ is a temporary column
    It refers to a computed field.

    and must not appear in any table in use; ‘time_measurements_test’ is presumably the table which holds t1 and t2; …
    This is the case for another clarification attempt according to database applications also together with the software “LibreOffice Base 7.1.0.3-985.1”.


    • Are there any additional data type conversion functions needed for the desired subtraction of a value from a time field by the means of the structured query language?
    • How do you think about to work with floating-point numbers for such an use case?

  6. #6
    Join Date
    Sep 2014
    Location
    Germany
    Posts
    702

    Default Re: Computing the difference between two time values by means of SQL

    Quote Originally Posted by elfring View Post
    Two columns of a table (for the database engine “HyperSQL” within the application “LibreOffice Base 7.1.0.3-985.1”) have got the data type “TIME”.
    I would like to calculate a difference for these fields.

    It seems that the following SQL statement is insufficient so far.

    SELECT ID, t1, t2, t2 - t1 AS duration FROM time_measurements_test


    I would appreciate your advices.

    Would you like to point further information sources out besides query examples which work with dates and time stamps?
    May be this

    https://www.hsqldb.org/doc/guide/bui...ime_arithmetic

    helps.

    Regards

    susejunky

  7. #7

    Question Re: Computing the difference between two time values by means of SQL

    Quote Originally Posted by susejunky View Post
    How much do the referenced (datetime) functions matter when the difference between TIME values (and not date/time stamps) should be determined?

  8. #8
    Join Date
    Sep 2014
    Location
    Germany
    Posts
    702

    Default Re: Computing the difference between two time values by means of SQL

    Quote Originally Posted by elfring View Post
    How much do the referenced (datetime) functions matter when the difference between TIME values (and not date/time stamps) should be determined?
    Did you work through the rest of that manual?

    If i recall correctly it contains a chapter on data types and explains how one can convert between different data types.

    Did you try to convert your TIME values to TIMESTAMP values and then use TIMESTAMPDIFF ?

    Regards

    susejunky

  9. #9
    Join Date
    Sep 2014
    Location
    Germany
    Posts
    702

    Default Re: Computing the difference between two time values by means of SQL

    Quote Originally Posted by susejunky View Post
    Did you work through the rest of that manual?
    Beware that manual is for HyperSQL database engine 2.6.0 whereas LibreOffice 7.x still uses HyperSQL database engine 1.8.0.10.

    However according to https://wiki.documentfoundation.org/...seitig_V70.pdf (i could not find an English Version of that document) the function DATEDIFF should do the job even without converting the TIME values.

    Regards

    susejunky

  10. #10

    Lightbulb Re: Computing the difference between two time values by means of SQL

    Quote Originally Posted by susejunky View Post
    Did you try to convert your TIME values to TIMESTAMP values and then use TIMESTAMPDIFF ?
    I got distracted by the key word “DATE” while I was looking for the support of an other time unit.
    An SQL statement like the following can fit to my application needs.

    Code:
    SELECT "day", "start", "stop",
           DATEDIFF( 'minute', "start", "stop" ) / 60.000 AS "duration"
    FROM "time_measurements";

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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