Symfony2 datetime best way to store timestamps?

Question

I don't know which is the best way to store a timestamp in the database. I want to store the entire date with hours minutes and seconds but it only stores the date ( for instance 2012-07-14 ) and i want to store 2012-07-14 HH:MM:SS. I am using the dateTime object. Here is the code:

In the controller:

$user->setCreated(new \DateTime());

In the entity:

/**
 * @var date $created
 *
 * @ORM\Column(name="created", type="date")
 */
private $created;

Is it better to store the date and the the time separately in the database ? or better to store all together like YYYY-MM-DD HH:MM:SS ? I will have then to compare dates and calculate the remaining times, so that is important in order to simplify the operations later. So what do you think ? can somebody help me?

Answer 1

You can use @Version like this:

/**
 * @var date $created
 *
 * @ORM\Column(name="created", type="datetime")
 * @ORM\Version
 */
private $created;

This will only work on a datetime type.

Answer 2

Building on @Pratt's answer I did this. I have 2 fields in my entities one for created and one for modified.

/**
* @ORM\Column(type="datetime")
 */
protected $created_at;

/**
* @ORM\Column(type="datetime")
 */
protected $modified_at;

And then using annotation I call this on prePersist and preUpdate

/**
 * @ORM\PrePersist
 * @ORM\PreUpdate
 */
public function updatedTimestamps()
{
    $this->setModifiedAt(new \DateTime(date('Y-m-d H:i:s')));

    if($this->getCreatedAt() == null)
    {
        $this->setCreatedAt(new \DateTime(date('Y-m-d H:i:s')));
    }
}

The function could be broken up into 2 functions one for create one for update, but this is working so I see no reason for the extra code when this is working properly.

Answer 3

The best way to store a timestamp in the database is obviously to use the timestamp column if your database supports that type. And since you can set that column to autoupdate on create, you dont even have to provide a setter for it.

There is a Timestampable behavior extension for Doctrine 2 which does exactly that from the userland side as well:

Timestampable behavior will automate the update of date fields on your Entities or Documents. It works through annotations and can update fields on creation, update or even on specific property value change.

Features:

• Automatic predifined date field update on creation, update and even on record property changes
• ORM and ODM support using same listener
• Specific annotations for properties, and no interface required
• Can react to specific property or relation changes to specific value
• Can be nested with other behaviors
• Annotation, Yaml and Xml mapping support for extensions

With this behavior, all you need to do is change your annotation to

/**
 * @var datetime $created
 *
 * @Gedmo\Timestampable(on="create")
 * @ORM\Column(type="datetime")
 */
private $created;

Then you dont need to call setCreated in your code. The field will be set automatically when the Entity is created for the first time.

Answer 4

In order to store the date of creation without using the Timestampable behaviour of doctrine, you can also use LifeCycle Callbacks, by adding the annotation @ORM\HasLifecycleCallbacks when you declare the class. Here is what would work in your case to store YYYY-MM-DD HH:MM:SS in the database.

/**
 * @ORM\Entity
 * @ORM\HasLifecycleCallbacks
 * @ORM\Table(name="yourTable")
 */
class Nasy
{

    /**
    * @ORM\Column(name="created", type="string", length=255)
    */

    private $created;
    /**
     * @ORM\PrePersist
     */
    public function doStuffOnPrePersist()
    {
        $this->created = date('Y-m-d H:i:s');
    }

Finally, if you have a problem of timezone, you could set the timezone in the session by using an event listener on login. Matt Drolette did an awesome work on his blog here. You will probably always be storing the time in the timezone your server is in anyway. Then you use the timezone set in the session to display the right time to the user. Good luck.