'Is it safe to store dates as a string in mysql?
I have to allow my users to make an update with 24 hours of delay from their previous update, so because date operations in this case are going to be on the backend, but in the web server, i can store Javascript Date as a string in the database and when i have to calculate the difference between 2 dates i can query the lastUpdateDate from the database and parse it to a JS Date Object, is this safe to do?
Solution 1:[1]
Is it safe to store dates as a string in mysql?
It is safe as long as the format that you use to represent your dates is unambiguous (that is, each value maps to a unique date).
But it is always inefficient not to use the proper datatype to store a value. Sooner or later, you will face the need to do some date computation in the database (sorting, filtering, adding, ...): storing your dates as strings will make such operation more complex that it has to (the overhead varies depending on the format your choose), and much less efficient (you would typically need to translate all the strings to dates before you can operate on them).
On the other hand, using the proper datatype from the start does not makes things more complicated on the frontend - especially in MySQL. You just need to format your strings properly ('YYYY-MM-DD HH:MI:SS') before passing them to the database, and MySQL will happily treat them as dates.
Solution 2:[2]
It is, unless your date strings are unique. You can specify UNIQUE clause for it. But there is no reason to do so, because MySql provides 5 built-in Date/Time data types(DATE, DATETIME, TIMESTAMP, TIME, YEAR(M)). Still if you save dates as a string, you might face problems later in extracting dates in right format.
Solution 3:[3]
I usually store date and time in both formats, as a string and in the native database format. Despite the disadvantages of some extra processing and extra storage space, I find it useful because all my search and reporting queries are based on the text format. This makes my application database-agnostic since text comparisons are the same across all major databases, whereas date and time formats vary significantly across different databases. I use the native database format stored dates and times for date-based calculations.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | GMB |
| Solution 2 | gpl |
| Solution 3 | Raza |