Formatting Drupal's UNIX timestamp dates

Drupal stores date/time value as int columns in MySQL. Its value is UNIX timestamp based. You will not be able to determine the actual date/time by selecting from the table.

Here's a convenient way to convert the date/time columns directly from SQL:

SELECT cid, data, FROM_UNIXTIME(created) FROM main_cache

You can also use this in the WHERE clause like below:

SELECT COUNT( * ) 
FROM  main_commerce_product 
WHERE FROM_UNIXTIME( created ) 
BETWEEN  '2013-07-17 00:00:00'
AND  '2013-07-17 23:59:59'

Here's the result: