Timeline for Should I use DATE or VARCHAR in storing dates in MySQL?
Current License: CC BY-SA 4.0
27 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 14, 2021 at 9:00 | history | tweeted | twitter.com/StackSoftEng/status/1360876457787064321 | ||
| Feb 13, 2021 at 15:16 | vote | accept | mizstereo | ||
| Feb 13, 2021 at 15:16 | |||||
| Feb 12, 2021 at 1:57 | comment | added | Eric Towers | How do you interpret "30 February 2020"? There are some calendaring applications for which this must be a date. There are some where this must not be a date. What's your application? | |
| Feb 11, 2021 at 12:41 | comment | added | ps2goat |
Once you deal with different time zones and multi-tier services, you'll wish for the dates to be returned from the database as strings. With so much serializing/deserializing going on, there's a big chance for offsets to get lost or interpreted incorrectly during default model binding of dates/datetimes (e.g., .NET MVC or JS dates in the browser). I had to tell the knex or objectionjs package (can't remember which was the cause) to bring dates back as strings, because it converted them to JS dates that then had my pc's offset applied. Argh.
|
|
| Feb 11, 2021 at 5:16 | answer | added | Panzercrisis | timeline score: 0 | |
| Feb 10, 2021 at 14:03 | comment | added | IMSoP |
@BernhardDöbler The values coming back from the DB really will be strings; I have seen database drivers with an option to construct DateTime objects automatically, but most leak the implementation detail that the driver represents everything as strings on the wire for portability. A particularly annoying case is (or at least was, I haven't used it recently) the PHP Postgres driver leaving booleans as their string representations, which are 't' and 'f'.
|
|
| Feb 10, 2021 at 13:13 | comment | added | Bernhard Döbler |
Does echo call a .toString() method on the data object, implicitly? What does var_dump show?
|
|
| Feb 10, 2021 at 12:20 | comment | added | marcelm |
"I don't understand as to why the DATE data type exists if you can casually store date objects as VARCHAR in the db." - Well, you can casually store integers in VARCHARs too. Doesn't mean you should ;)
|
|
| Feb 10, 2021 at 5:34 | comment | added | Acccumulation | "In comparing such objects I would just convert them into datetime objects in php and then do the comparing." Why are you using an SQL database if you're not going to use SQL? | |
| Feb 10, 2021 at 4:31 | answer | added | ghellquist | timeline score: 3 | |
| Feb 10, 2021 at 3:33 | answer | added | Xavon_Wrentaile | timeline score: 11 | |
| Feb 10, 2021 at 0:25 | comment | added | Crazy Dino | Also, please use the correct formats and think of the poor people who will have to maintain the system after you :D | |
| Feb 10, 2021 at 0:24 | comment | added | Crazy Dino | Lets just say you have 100 orders a year for the last 10 years. If you wanted all the orders within the last year, what is more logicial and optimised, get the database to return only the records you need OR loop through all the records yourself and collect the ones that match your criteria. | |
| Feb 10, 2021 at 0:24 | comment | added | T. Sar | You can also store integers in VARCHAR columns. Should you, however? Obviously not, since integers are way faster to operate than strings. Same goes for every other datatype. Varchar should be one of the last options for storing stuff, not the default one. | |
| Feb 9, 2021 at 20:14 | history | became hot network question | |||
| Feb 9, 2021 at 14:44 | comment | added | Laiv | Some databases bind triggers to date columns as well. | |
| Feb 9, 2021 at 14:29 | answer | added | Lie Ryan | timeline score: 13 | |
| Feb 9, 2021 at 13:42 | review | Close votes | |||
| Feb 14, 2021 at 3:02 | |||||
| Feb 9, 2021 at 13:09 | answer | added | IMSoP | timeline score: 24 | |
| Feb 9, 2021 at 12:41 | comment | added | Philip Kendall | I suggest you benchmark your method against doing the same comparisons natively in the database. Don't be surprised if the database is 100x quicker. | |
| Feb 9, 2021 at 12:39 | comment | added | mizstereo | In comparing such objects I would just convert them into datetime objects in php and then do the comparing... | |
| Feb 9, 2021 at 12:33 | comment | added | Philip Kendall |
Or "how many days are there between date1 and date2?" or "what ISO week number is date1?" or "select all records where date1 is a Monday?" etc etc etc
|
|
| Feb 9, 2021 at 12:30 | answer | added | Phill W. | timeline score: 62 | |
| Feb 9, 2021 at 12:30 | comment | added | mizstereo |
@PhilipKendall by trying to find the difference you mean comparing? E.g. comparing whether date1 > date2 (assuming these dates are stored as VARCHAR)
|
|
| Feb 9, 2021 at 12:21 | comment | added | Philip Kendall |
What happens if you try to find the difference of two VARCHAR objects?
|
|
| Feb 9, 2021 at 12:17 | review | First posts | |||
| Feb 9, 2021 at 12:33 | |||||
| Feb 9, 2021 at 12:14 | history | asked | mizstereo | CC BY-SA 4.0 |