How to best store a timestamp or date in a database?

Question

When I need to store time/date information, how is it best to be stored in a database?
If I store them as a String representation e.g. get current timestamp (using Date for example) and store the String representation in the DB is it a good idea?
What is the best practice for this? What are any cons?

Answer 1

The best practice is to use a TIMESTAMP or DATE type in the database. All major database vendors support these SQL standard data types.

Apart from performance and storage optimisation, you'll gain expressiveness and you'll be able to use a lot of datetime manipulation functions and operations directly in the database, depending on the database you're using. Think about comparing dates, adding dates, adding intervals to dates (e.g. what day is tomorrow), etc...

Note that...

• If you're using Oracle, beware that DATE also contains time information
• If you're using SQLite, beware that datetime types may have numeric or text affinity. I.e. they're stored as numbers or text.
• If you're using SQL Server, Sybase (ASE and SQL Anywhere), SQLite, the dialect-specific version of the TIMESTAMP data type is called DATETIME (SQL Server's TIMESTAMP is actually a VARBINARY(8))
• At least H2, HSQLDB, MySQL, Sybase SQL Anywhere support both TIMESTAMP and DATETIME data type synonyms.

Answer 2

Its best to use a timestamp datatype in both MySQL and T-SQL.

Using timestamps allows you to perform calucations on the data far easier queries, addings or substrating days, months, years whatever you like. Also ordering is easier.

Timestamps also contain a timezone part to, so if you ever need to internationalise your site it would be easier.

Most languages such as PHP also have functions to display timestamps in a format of your choosing so you can convert timestamps to more readable formats.

Finally javascript/jquery plugins often require full timestamps to perform calculations on dates and times jQuery timeago for example.