Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2004
    Posts
    96

    Unanswered: Adding 2 hours to the current time problem

    Hello,
    I am trying to add 2 hours to the current time to compare it with a time in my db, I have try several different ways after looking at the MySQl manual but can't get it to work:

    Code:
    SELECT ADDTIME(CURTIME(), INTERVAL 2 HOUR);
    Thanking you

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Did you try :

    Code:
    select ADDTIME(CURTIME(), '02:00:00.000000') 
    from DUAL;
    or :

    Code:
    select SEC_TO_TIME(TIME_TO_SEC(CURTIME()) + 7200) 
    from DUAL;
    Regards,

    RBARAER

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fyi there is no DUAL table in mysql (unless you create one yourself)

    in mysql it is not necessary to select from a table

    SELECT now(), ADDDATE(now(), INTERVAL 2 HOUR) as newtime

    --> 2004-10-27 08:04:42 2004-10-27 10:04:42
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry, Rudy, it may only be a "dummy" feature, but it now exists (OK I'm becoming too used to Oracle ). See the MySQL Manual :

    From MySQL 4.1.0 on, you are allowed to specify DUAL as a dummy table name in situations where no tables are referenced:

    mysql> SELECT 1 + 1 FROM DUAL;
    -> 2

    DUAL is purely a compatibility feature. Some other servers require this syntax.
    Best Regards,

    RBARAER

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, whaddya know, ya learn sumpin new every day!!

    thanks for the correction

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2004
    Posts
    96
    Thanks guys,
    I can now select and add the time but when comparing with times in the DB it is not working as I expected, i am trying to select only records where the time in the database + 1 hour is superior at the current time but all records are returned:

    Code:
    SELECT codeId, codetime, ADDDATE(codetime, INTERVAL 1 HOUR) as limittime FROM tbllogin_codes WHERE ADDDATE(codetime, INTERVAL 1 HOUR) > curtime();
    Here is the result:
    +----------------+---------------------+---------------------+
    | codeId | codetime | limittime |
    +----------------+---------------------+---------------------+
    | 10988829451451 | 2004-10-27 16:31:00 | 2004-10-27 17:31:00 |
    | fdfd | 2004-10-27 15:31:00 | 2004-10-27 16:31:00 |
    | 10988910604261 | 2004-10-27 05:31:00 | 2004-10-27 06:31:00 |
    +----------------+---------------------+---------------------+

    Any idea where I am going wrong there?

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    ADDDATE() returns a date-time, not a time, so try using CURDATE() instead of CURTIME() :

    Code:
    SELECT codeId, codetime, ADDDATE(codetime, INTERVAL 1 HOUR) as limittime 
    FROM tbllogin_codes 
    WHERE ADDDATE(codetime, INTERVAL 1 HOUR) > curdate();
    Regards,

    RBARAER

  8. #8
    Join Date
    Aug 2004
    Posts
    96
    Hello,
    i have changed from time to date but it is still not working as expected, it still returns very records that have the smae date as the current date regardless of the time, should I change the DB and use a time field in stead?
    Regards,

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Did you try :
    Code:
    SELECT codeId, codetime, SEC_TO_TIME(TIME_TO_SEC(codetime) + 7200) as limittime 
    FROM tbllogin_codes 
    WHERE SEC_TO_TIME(TIME_TO_SEC(codetime) + 7200)  > curtime();
    or :
    Code:
    SELECT codeId, codetime, ADDTIME(codetime, '02:00:00.000000') as limittime 
    FROM tbllogin_codes 
    WHERE ADDTIME(codetime, '02:00:00.000000') > curtime();
    as I already suggested ?

    Regards,

    RBARAER

  10. #10
    Join Date
    Aug 2004
    Posts
    96
    Hello,
    the second suggestion is giving me an error, i don't seem to be able to use ADDTIME()

    The second version doesn't give an error but return just about all records in the table, I have added the current time in the query:

    +----------------+---------------------+-----------+----------+
    | codeId | codetime | limittime | thetime |
    +----------------+---------------------+-----------+----------+
    | 10988957459411 | 2004-10-27 17:49:05 | 19:49:05 | 13:55:39 |
    | 10988968023791 | 2004-10-27 18:06:42 | 20:06:42 | 13:55:39 |
    | 10988969316451 | 2004-10-27 18:08:51 | 20:08:51 | 13:55:39 |
    | 10988970206761 | 2004-10-27 18:10:20 | 20:10:20 | 13:55:39 |
    | 10988970790201 | 2004-10-27 18:11:19 | 20:11:19 | 13:55:39 |
    | 10988972871131 | 2004-10-27 18:14:47 | 20:14:47 | 13:55:39 |
    | 10988973890201 | 2004-10-27 18:16:29 | 20:16:29 | 13:55:39 |
    | 10988975970041 | 2004-10-27 18:19:57 | 20:19:57 | 13:55:39 |
    | 10988976488631 | 2004-10-27 18:20:48 | 20:20:48 | 13:55:39 |
    | 10990530569531 | 2004-10-29 13:30:56 | 15:30:56 | 13:55:39 |
    +----------------+---------------------+-----------+----------+

    Thanking you for your help,
    olivier

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Ok,

    The problem comes from your "codetime" field being a DATE, not a TIME... We are just turning MySQL mad with DATETIME / TIME functions or convertions ! TIME_TO_SEC should only be used with TIME, and ADDTIME(datetime) cannot be correctly compared to a time. And unfortunately, when we tried CURDATE(), it was returning a date without time portion. Use Now(), which returns the current DATE AND TIME :
    Code:
    SELECT codeId, codetime, ADDTIME(codetime, '02:00:00.000000') as limittime 
    FROM tbllogin_codes 
    WHERE ADDTIME(codetime, '02:00:00.000000') > Now();
    That should work.

    Regards,

    RBARAER

  12. #12
    Join Date
    Aug 2004
    Posts
    96
    hello,
    more bad news, I am getting a error 1064, check the manual.............
    It seems that every time I try to use ADDTIME() i get an error, thus I use one of the latest version.
    I have try to change the field to a time instead of a date / time but I get the same error, have also try different suggestions from above without any result!

    Just can't figure it out!

    Any idea what the problem could be?

    Thanking you,
    Olivier

  13. #13
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This one does not work even with a TIME field ?

    Code:
    SELECT codeId, codetime, SEC_TO_TIME(TIME_TO_SEC(codetime) + 7200) as limittime 
    FROM tbllogin_codes 
    WHERE SEC_TO_TIME(TIME_TO_SEC(codetime) + 7200)  > curtime();

  14. #14
    Join Date
    Aug 2004
    Posts
    96
    No it is returning all the rows

  15. #15
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Are you sure ? Are you sure that, in the rows you posted before, the last one (provided the date part is not here anymore) is still returned ? Other ones should be, but not the last one. Of course, since it is about 17:00, if all your rows are like time + 2 hours = 20:00, they will all be returned.

    Please check this.

    Regards,

    RBARAER

Posting Permissions

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