add compare date time

[Kurt111780]

Hello,

In one script I need to get the current date and time then add a certain number of hours or days to it before storing it in the database.

Then on another page a script will compare the current date and time to the one stored in the database. if ($DBtime >= $currTime)

I was going to store the date using this format $timeStamp = date("YmdHis"); but then how can I add days or hours to it? Should I use DateTime field type in MySql?

Thanks,
Kurt

It's only easy when you know how.

http://www.seedandgarden.com

 

[jpadie]

i would use a varchar or numeric field to store the date time information and store the timestamp in the database as a unix timestamp (number of seconds elapsed since the beginning of the unix epoch). then converting all your elapsed time into seconds should make light work of the maths.

you can convert either way easily enough with strtotime() and date() functions. just be sure to be consistent in your treatment otherwise you can get valid results that are incorrect and this can take hours to debug...

 

[Kurt111780]

Hello,

I guess I'll use varchar type. This works good

echo date('YmdHis', strtotime('+7 days'));
echo "<br /><br />";
echo date('YmdHis', strtotime('+3 hours'));

Kurt

It's only easy when you know how.

http://www.seedandgarden.com

 

[KarveR]

use timestamp and mysql's date_add function, its far easier

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jpadie]

KarverR: is this syntax common across all/most SQL database engines?

I'm interested because personally i prefer to use db abstraction layers so that i can reuse my code for clients independent of their db engines. this leads me to keep my sql as simple as possible to maximise the likelihood of the code working cross-platform.

 

[KarveR]

It seems to be common for mssql, pgsql, oracle and ysql .. others you'll have to figure out.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Kurt111780]

KarveR,

How would you use timestamp when comparing and adding like I wanted to do in my orginal post? Seems easier for me to use the php date and strtotime functions.

Kurt

It's only easy when you know how.

http://www.seedandgarden.com

 

[KarveR]

for mysql

select date_add(curdate(), interval 30 day);

this selects todays date and adds 30 days.

select date_sub(curdate(), interval 15 day);

this subtracts 15 days from today.

see the date and time functions manual page for more info, I think you'll find it very enlightening.


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.