Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2016
    Posts
    1

    Unanswered: Hour, minute and integer

    I have a table which I need to query using dynamic queries running at 15 minute intervals. The table has a column 'starttime' which is smallint and stores the time of the row in the format hhmm. Typically for a day, there will be rows with starttime values like 100, 115, 130, 200, 230...1000, 1030, and so on. The values represent the time of day, 1 am, 1.15 am 1.30 am, 2 am, 2.30... 10 am, 10.30 am and so on. I need to write a query to get a row of data for the most current interval i.e. if current time is 6.00 pm, then I should fetch the row which has starttime as 1745. I can get the current time in hhmm format using:
    SELECT CURRENT:: DATETIME HOUR TO HOUR::CHAR(2)::int||
    CURRENT:: DATETIME MINUTE TO MINUTE::CHAR(2)::INT
    FROM SYSTABLES WHERE TABID=1

    However, this returns a string, and cannot be used to compare against 'starttime' which is int. Is there a way to write a query where:

    select * from table where starttime + 15 > SELECT CURRENT:: DATETIME HOUR TO HOUR::CHAR(2)::int||
    CURRENT:: DATETIME MINUTE TO MINUTE::CHAR(2)::INT
    FROM SYSTABLES WHERE TABID=1

    Is there a better way of achieving what is needed?

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi

    this would be a perfect use case for TimeSeries, but it is a bit late now...
    Using a smallint to store the time is not a good idea because you'll need complex and probably inefficient SQL statements to get what you want.

    What I would do is to add a DATETIME HOUR TO MINUTE column which will receive the time value (ex: 01:00, 01:15 ....
    maybe add a trigger that updates that value at runtime from the smallint one, or change the application directly.

    Hoping that you never have to query values between one day and another day, or between one month and another.... In that case you will need a DATETIME YEAR TO MINUTE.

    Saving time at db design is never a good thing, because this will push the developers to write complex and hard to maintain queries that generally are unresponsive... Again, consider using TimeSeries for that kind of things, which has tremendous advantages.
    Check here as a starter
    You can also check for presentations about TimeSeries in the iiug website , in the member area/Conference downloads.
    IIUG Membership is free and offers a lot of advantages for the Informix community.
    Eric



    Again,

Posting Permissions

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