Adding 2 hours to the current time problem

oliflorence · #1 (**permalink**) 10-27-04, 09:52

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

RBARAER · #2 (**permalink**) 10-27-04, 10:32

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

r937 · #3 (**permalink**) 10-27-04, 11:05

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

RBARAER · #4 (**permalink**) 10-27-04, 11:13

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 :

Quote:

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

r937 · #5 (**permalink**) 10-27-04, 11:20

well, whaddya know, ya learn sumpin new every day!!

thanks for the correction

oliflorence · #6 (**permalink**) 10-27-04, 11:58

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?

RBARAER · #7 (**permalink**) 10-27-04, 13:04

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

oliflorence · #8 (**permalink**) 10-29-04, 06:47

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,

RBARAER · #9 (**permalink**) 10-29-04, 08:09

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

oliflorence · #10 (**permalink**) 10-29-04, 09:01

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

RBARAER · #11 (**permalink**) 10-29-04, 09:46

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

oliflorence · #12 (**permalink**) 10-29-04, 10:03

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

RBARAER · #13 (**permalink**) 10-29-04, 10:20

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();

oliflorence · #14 (**permalink**) 10-29-04, 10:47

No it is returning all the rows

RBARAER · #15 (**permalink**) 10-29-04, 11:24

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

Internet Services

Web Development

Digital Marketing

Consumer Tech