MySQL dates

PhpBB stores dates in INT fields in Unix time format, meaning the integer value equals to the number of seconds since 1/1/1970.

To view the date in standard format, use FROM_UNIXTIME(field) function.

select user_id, username, FROM_UNIXTIME(user_regdate) as d from phpbb_users

To select a time interval, we need to use FROM_UNIXTIME( ) function with a DATE_ADD() or DATE_SUB()

Example: select fields created less than 23 hours ago:

select user_id from phpbb_users where
FROM_UNIXTIME(user_regdate) > DATE_SUB(NOW(), interval 23 hour)

mySQL date functions reference: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Advertisements