add datetime field value to integer field value in mysql

Question

Is it possible to add one datetime field value like 2013-06-22 09:46:00 to one or more integer field value like1(represents days column)2(represents hours column) then the result obtain after adding datetime value to integer must be in datetime form, so that i can compare with another datetime field value. Like if i get the result after adding with the integer field 2013-06-23 11:46:00, i wants to compare it with another datetime field value, so that if the result value is greater than the compared value then only it w'll give me result.

I know how it w'll work in php but i wants to do through query.

Any help will be appreciated.

Why do you need the seperate columns? Thats just doubling the ammount of space required to insert a row. — CP510
– CP510, Commented Jun 22, 2013 at 16:47

Abhay · Accepted Answer · 2013-06-22 17:28:19Z

1

I think what you need is the MySQL's date_add() function - http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add.

For example, DATE_ADD('2013-06-22 09:46:00', INTERVAL 1 DAY), where you may replace the date-time and day interval with the corresponding column names.

UPDATE Let us assume, the date-time field is called FIELDA and the integer field is called FIELDB representing days, then the function might be called as:

DATE_ADD(FIELDA, INTERVAL FIELDB DAY);

If however the integer field represent hours, you may call it as:

DATE_ADD(FIELDA, INTERVAL FIELDB HOUR);

Hope above helps!

edited Jun 22, 2013 at 17:28

answered Jun 22, 2013 at 17:07

Abhay

6,6852 gold badges26 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kumari Manisha Over a year ago

thanks..but here i don't have integer value, i have to pass day value from the field of same table.. will you please explain me how i can do that.

Kumari Manisha Over a year ago

As i mentioned above, i have to add both day and hour and i am trying this DATE_ADD(FIELDA, INTERVAL FIELDAY:FIELDHOUR DAY_HOUR); but its not working

Abhay Over a year ago

@KumariManisha, please try DATE_ADD(FIELDA, INTERVAL 'FIELDDAY FIELDHOUR' DAY_HOUR);

Abhay Over a year ago

@KumariManisha, I'm not sure if you're getting an error? The exact syntax is as mentioned in my last comment - DATE_ADD(FIELDA, INTERVAL 'FIELDDAY FIELDHOUR' DAY_HOUR);, i.e. single quotes are required in the "interval" and there should be a space (instead of colon) between FIELDDAY AND FIELDHOUR

Collectives™ on Stack Overflow

add datetime field value to integer field value in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related