Store current time in timestamp

Question

I have a field named: last_dl in MySQL database.
I don't know what type it should be. At first I want to store current time in it, and in future, after a user request, check if 72 hours passed. then do something.

I'm confused what type to choose. Should I use MYSQL functions or PHP functions? Do I need timestamp or not. How to convert back that timestamp in database?

sql has datediff function but only you know how you are going to interrogate it and how use that data... — MiloBellano, Dec 14 '17 at 09:02
What's wrong with using a datetime datatype in your database? — Mark Baker, Dec 14 '17 at 09:03
Check this https://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp — Md Monjur Ul Hasan, Dec 14 '17 at 09:08

score 2 · Accepted Answer · answered Dec 14 '17 at 09:05

2

I would go with a DATETIME field in mySql. Then from php you can check if 72 hours have passed since then.

$dbdate = strtotime($datefromdb);
if (time() - $dbdate > 60 * 60 * 72) {
    // 72 hours passed
}

answered Dec 14 '17 at 09:05

Fabio Marzocca

1,573
16
36

ino · Answer 2 · 2017-12-14T09:34:52.123

Yes, store the value of last_dl in the database with the DATETIME format.

Then all you need is run query in database and the PHP will get the result ready:

SELECT HOUR(TIMEDIFF(`last_dl`, NOW())) as 'hours diff',
IF(HOUR(TIMEDIFF(`last_dl`, NOW())) > 72,'YES' , 'NO') as 'age check passed'
FROM `table_name`
;

Result is: Assume the last_dl has value '2017-12-11 15:45:57.005678'

hours diff age check passed
89 YES
99 YES
65 NO
75 YES
41 NO
51 NO
17 NO

See it live: http://sqlfiddle.com/#!9/b7c11d/1

EDIT Tuned the example ;)

Store current time in timestamp

2 Answers2