2

I have a Script that uses Date to pick the day name and number of it, Something like the following

2018-06-04 =becomes=> Monday(04, June)
2018-06-04 =or becomes=> Monday(04) // In no need for Month

So What I do is creating a Column in the table using DATETIME datatype, Then I set the values like this

000-06-04 //case1
000-00-04 //case2

Is that a proper way to do this, Or is it better if I use VARCHAR(18) datatype and write it directly like this

Monday(04, June) //case1
Monday(04) //case2

Using DATETIME requires me to use DATE_FORMAT() later on, And using VARCHAR(18) doesn't.

SELECT DATE_FORMAT(date, '%W(%d, %m)') FROM table //case1
SELECT date FROM table //case2

I'm don't care about ease of use But performance.

4
  • Do you really needs to format data in the server-side? Why not let UI layer handle it? They normaly can handle it better an if in a client-side (browser) can do it localized using the client CPU
    – jean
    Jun 4, 2018 at 18:25
  • 2
    If you're never going to modify the date again, you CAN save it as a varchar and in fact save a albeit trivial amount of time not converting it. However, any join, or where clause you use it in will have a NON-trivial performance penalty, so I'd stick to datetime.
    – RandomUs1r
    Jun 4, 2018 at 18:45
  • @jean Wouldn't that make the website pretty slow for weak and old devices? since it depends on the client device?
    – Toleo
    Jun 4, 2018 at 18:54
  • 1
    Unless you are rendering hundreds (maybe thousands) pages reports on the device it will smoth handle it. Also we generaly worry more about how it will show on new devices since old devices means old browsers and it's not pratical to spent too much resrouces just to keep retro compatibility to a negligible fraction of potential users
    – jean
    Jun 4, 2018 at 19:00

2 Answers 2

4

As stated by manual DATE type internally represented by 3 bytes that is smallest possible for the required type of data. TIMESTAMP is represented internally as BIGINT seconds since epoch (4 bytes) but TIMESTAMP is always forced to be converted to/from the UTC if server's TZ is not UTC. That can cause significant overhead. DATE is compatible with date-related functions like week() or monthname() so it's look like the best choice.

DATE is far more efficient for search/sort/join/group/between than VARCHAR(18) so VARCHAR() isn't even an option.

3

Any column that MySQL can treat blindly as a string (or blob) may as well be such.

Any column that MySQL needs to compare, sort, etc, should be stored in some way that is convenient for MySQL, even if it means converting while storing or fetching. As a corollary to this, do not worry about the cost of DATE_FORMAT() and MONTH() when you need the display format to be anything other than yyyy-mm-dd.

DATE has 3 components, not 2: year-month-day. If you don't have a year, then you really don't have what MySQL thinks of as a "date". If the user does not provide a Year, either explicitly or implicitly, then DATE is not practical -- there is no yearless DATE. "Monday June 4" occurs in about 14 years every century.

(Not directly related.) In almost all situations, it is not a good idea to store DATE and TIME separately. It is easy to split a DATETIME into its parts; it is hard to go the other direction (eg, for a range test or ORDER BY).

Should you store the day of month, day of week, and month separately? If you manipulate or test them separately, then perhaps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.