Dates in MySQL

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s