Introduction

Being a PHP developer, you are no way hiding from PHP’s date() function.
In case of Magento, it makes use of timezone which is configured from the backend (System > Configuration > General > Locale Options > Timezone) for formatting/displaying date/time.
And this makes the results obtained with the PHP’s date() function and the Magento’s one a bit different.
For example:
Normal PHP Way

$currentTimestamp = time();
echo $date = date('Y-m-d', $currentTimestamp); //2011-12-11 (current date of the server)

Magento Way

$currentTimestamp = Mage::getModel('core/date')->timestamp(time()); //Magento's timestamp function makes a usage of timezone and converts it to timestamp
echo $date = date('Y-m-d', $currentTimestamp); //The value may differ than above because of the timezone settings.

Since Magento is meant to be for the multi-website / multi-lingual / multi-locale purpose, it’s always a good practice to format the date using Magento’s date/time function.
AFAIK, there is no need for conversion of date/time while inserting into the database. The formatting/conversion thing is only done at the frontend level for displaying purposes.

Some Useful Examples

1. Displaying the current date

$currentTimestamp = Mage::getModel('core/date')->timestamp(time());
echo $currentDate = date('Y-m-d', $currentTimestamp);

OR

echo $currentDate = Mage::getModel('core/date')->date('Y-m-d');

2. Formatting any date in any format

$anyDate = '2011-12-11';
$dateTimestamp = Mage::getModel('core/date')->timestamp(strtotime($anyDate));
echo $currentDate = date('d.m.Y', $dateTimestamp);

OR

$anyDate = '2011-12-11';
echo $currentDate = Mage::getModel('core/date')->date('d.m.Y', strtotime($anyDate));

3. Predefined date formatting

$dateToFormat = '2011-12-11';
Mage::helper('core')->formatDate($dateToFormat, 'medium', false);

Note: Mage_Core_Helper_Data::format() has following arguments

/**
 * Format date using current locale options
 *
 * @param   date|Zend_Date|null $date in GMT timezone
 * @param   string $format (full, long, medium, short)
 * @param   bool $showTime
 * @return  string
 */
public function formatDate($date=null, $format='short', $showTime=false)
{
....
}

Hope this gave some info about Date/Time functionality available in Magento.
Thanks for reading!
Cheers!!