What is the best way to store a timestamp or date in a database?

Question

When I need to store time and time information, what is the best way to store it in a database?
If I save them as a String representation, for example. getting the current timestamp (e.g. using Date ) and saving the String representation in the DB is a good idea?
What is the best practice for this? What are any cons?

Answer 1

Best practice is to use the type TIMESTAMP or DATE in the database. All major database providers support these standard SQL data types.

In addition to optimizing performance and storage, you will get expressiveness, and you can use many functions and operations of manipulating time dates directly in the database, depending on the database used. Consider comparing dates, adding dates, adding intervals to dates (for example, what day will be tomorrow), etc.

Note that...

• If you are using Oracle, be careful that DATE also contains time information.
• If you are using SQLite, beware that datetime types may have numeric or text similarities. That is, they are saved as numbers or text.
• If you use SQL Server, Sybase (ASE and SQL Anywhere), SQLite, the specific version of the TIMESTAMP data TIMESTAMP is called DATETIME (SQL Server TIMESTAMP is actually VARBINARY(8) )
• At least H2, HSQLDB, MySQL, Sybase SQL Anywhere support the synonyms of TIMESTAMP and DATETIME data types.

Answer 2

It is best to use the timestamp data type in both MySQL and T-SQL.

Using timestamps allows you to perform costing on data with much easier queries, additions or days of lookup, months, years, whatever you are. In addition, ordering is easier.

Timestamps also contain part of the time zone, so if you ever need to internationalize your site, it would be easier.

Most languages, such as PHP, also have functions for displaying timestamps in a format of your choice so that you can convert timestamps to more readable formats.

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

Answer 3

The best practice is to use the DATE / TIMESTAMP data type available from your database provider, one of the advantages of having a basic data type is that “in the future you can request specific time conditions”, for example, in the future, which may take up to

• Generate a report for a specific month / year
• Create a report between two dates between 01/05/11 and 01/02/12, etc.
• In order to display a wise report (ietotal sales, August 15, 11), everything, although it can also be done using the String data type, I believe that comparing dates is much faster than String representation (char or Varchar, etc.), 4.....

In addition to the above conditions, they have much more cases for which it is more reasonable to use Date? TimeStamp instead of String ....