[SQL]: Data Types

SQL

09/28/2019

VARCHAR & CHAR

CHAR has a fixed length. If the length of data is too long, it will truncate. If it is too short, it will add spaces(right-pads) to the fixed length. However, when CHAR values are retrieved, trailing spaces are removed unless PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled

VARCHAR & CHAR can be any value from 0 to 255

CHAR is faster for fixed length text. Example:

State abbreviations: WI, CA
Gender: M/F
Yes/No flags: Y/N

Otherwise, use VARCHAR!

CHAR vs VARCHAR comparison for storage

TEXT

+-------------------------------------------------------+
| Value     | CHAR(4) | Storage  | VARCHAR(4) | Storage |
+-------------------------------------------------------+
| ''        | '    '  | 4 bytes  | ''         | 1 byte  |
| 'ab'      | 'ab  '  | 4 bytes  | 'ab'       | 3 byte  |
| 'abcd'    | 'abcd'  | 4 bytes  | 'abcd'     | 5 byte  |
| 'abcdefg' | 'abcd'  | 4 bytes  | 'abcdefg'  | 5 byte  |
+-------------------------------------------------------+

Note that CHAR uses slightly less memory when the character length is 4 (4 bytes vs 5 bytes)

Decimals

SQL

SELECT CREATE TABLE(
    somedecimal DECIMAL(5,2);
);

5 is total number of digits both before and after .
2 is digits after decimal point
Example: 999.99 (highest possible value)

Example

SQL

CREATE TABLE somedecimal( price DECIMAL(5, 2) );
-- Insert data
INSERT INTO somedecimal VALUES (3);
INSERT INTO somedecimal VALUES (31231233);
INSERT INTO somedecimal VALUES (77.77);
INSERT INTO somedecimal VALUES (123.8888);
INSERT INTO somedecimal VALUES (1.9999);

TEXT

+--------+
| price  |
+--------+
|   3.00 | <- Two decimals given 2nd parameter
| 999.99 | <- Changed to largest possible #
|  77.77 | <- Unchanged
| 123.89 | <- Rounded up
|   2.00 | <- Also rounded
+--------+

FLOAT & DOUBLE

DECIMALs calculations are exact while FLOAT or DOUBLE calculations are approximate.

FLOAT & Double store larger numbers using less space but it comes at the cost of precision--they won't be as accurate as DECIMALS.

TEXT

+-----------+---------------+------------------+
| Data Type | Memory Needed | Precision Issues |
+-----------+---------------+------------------+
| FLOAT     | 4 Bytes       | ~7 digits        |
| DOUBLE    | 8 Bytes       | ~15 digits       |
+-----------+---------------+------------------+

Try using DECIMALS unless you know that precision doesn't matter.

Example

SQL

CREATE TABLE stuff(price FLOAT);
INSERT INTO stuff VALUES (77.43);
INSERT INTO stuff VALUES (9977.45);
INSERT INTO stuff VALUES (12345.67);
INSERT INTO stuff VALUES (5544332211.11);

TEXT

+------------+
| price      |
+------------+
|      77.43 |
|    9977.45 |
|    12345.7 |
| 5544330000 |
+------------+

Notice the precision. It doesn't just truncate it, but stores different number

Dates & Times

DATE: Stores date without time in format of YYYY-MM-DD.
TIME: Store time without date in format of HH:MM:SS. Not as common as DATE.
DATETIME: Store both in formate of YYYY-MM_DD HH:MM:SS. Very commonly used.

Example

SQL

CREATE TABLE ppl (
  name VARCHAR(100),
  birthdate DATE,
  birthtime  TIME,
  birthdt DATETIME
);
INSERT INTO ppl
VALUES('Paul', '1956-11-13', '10:03:33', '1956-11-13 10:03:33');
INSERT INTO ppl
VALUES('Larry', '1967-12-23', '04:20:47', '1967-12-23 04:20:47');

TEXT

+-------+------------+-----------+---------------------+
| name  | birthdate  | birthtime | birthdt             |
+-------+------------+-----------+---------------------+
| Paul  | 1956-11-13 | 10:03:33  | 1956-11-13 10:03:33 |
| Larry | 1967-12-23 | 04:20:47  | 1967-12-23 04:20:47 |
+-------+------------+-----------+---------------------+

CURDATE, CURTIME, NOW

SQL

INSERT INTO ppl
VALUES( 'John', CURDATE(), CURTIME(), NOW() );

TEXT

+-------+------------+-----------+---------------------+
| name  | birthdate  | birthtime | birthdt             |
+-------+------------+-----------+---------------------+
| Paul  | 1956-11-13 | 10:03:33  | 1956-11-13 10:03:33 |
| Larry | 1967-12-23 | 04:20:47  | 1967-12-23 04:20:47 |
| John  | 2019-09-29 | 00:44:35  | 2019-09-29 00:44:35 |
+-------+------------+-----------+---------------------+

CURDATE() - gives current date
CURTIME() - gives current time
NOW() - gives both current date and time

Formating Date & Times

List of DATE and TIME functions

Extract elements from Date

SQL

SELECT name, birthdate,
YEAR(birthdate), MONTH(birthdate), DAY(birthdate)
FROM ppl;

TEXT

+-------+------------+-----------------+------------------+----------------+
| name  | birthdate  | YEAR(birthdate) | MONTH(birthdate) | DAY(birthdate) |
+-------+------------+-----------------+------------------+----------------+
| Paul  | 1956-11-13 |            1956 |               11 |             13 |
| Larry | 1967-12-23 |            1967 |               12 |             23 |
| John  | 2019-09-29 |            2019 |                9 |             29 |
+-------+------------+-----------------+------------------+----------------+

YEAR(), MONTH(), DAY() extracts certain information from DATE.

SQL

SELECT name, birthdate, DAYNAME(birthdate),
 DAYOFWEEK(birthdate), DAYOFYEAR(birthdate)
FROM ppl;

TEXT

+-------+------------+--------------------+----------------------+----------------------+
| name  | birthdate  | DAYNAME(birthdate) | DAYOFWEEK(birthdate) | DAYOFYEAR(birthdate) |
+-------+------------+--------------------+----------------------+----------------------+
| Paul  | 1956-11-13 | Tuesday            |                    3 |                  318 |
| Larry | 1967-12-23 | Saturday           |                    7 |                  357 |
| John  | 2019-09-29 | Sunday             |                    1 |                  272 |
+-------+------------+--------------------+----------------------+----------------------+

More examples for DAYNAME(), DAYOFWEEK(), DAYOFYEAR(). There are also MONTHNAME and many more.

Extract elements from Time

SQL

SELECT name, birthtime, HOUR(birthtime),
 MINUTE(birthtime), SECOND(birthtime)
FROM ppl;

TEXT

+-------+-----------+-----------------+-------------------+-------------------+
| name  | birthtime | HOUR(birthtime) | MINUTE(birthtime) | SECOND(birthtime) |
+-------+-----------+-----------------+-------------------+-------------------+
| Paul  | 10:03:33  |              10 |                 3 |                33 |
| Larry | 04:20:47  |               4 |                20 |                47 |
| John  | 00:44:35  |               0 |                44 |                35 |
+-------+-----------+-----------------+-------------------+-------------------+

SQL

SELECT name, birthdate,
    CONCAT(MONTHNAME(birthdate), ' ', DAY(birthdate), ' ', YEAR(birthdate)) AS "dif format"
FROM ppl;

Above is not efficient. You can use DATE_FOTMAT instead

DATE_FORMAT

Besides using the methods above, there is also a convenient way of using DATE_FORMAT. Reference and examples are below:

SQL

SELECT DATE_FORMAT('2009-08-15 23:53:00', '%W, %M, %Y')
AS "date";

TEXT

+------------------------+
| date                   |
+------------------------+
| Saturday, August, 2009 |
+------------------------+

SQL

select DATE_FORMAT(now(), '%M %D at %l:%i');

TEXT

+--------------------------------------+
| DATE_FORMAT(now(), '%M %D at %l:%i') |
+--------------------------------------+
| September 29th at 3:28               |
+--------------------------------------+

SQL

SELECT DATE_FORMAT('2009-08-15 23:53:00', '%W-%M-%Y')
AS "date";

TEXT

+----------------------+
| date                 |
+----------------------+
| Saturday-August-2009 |
+----------------------+

SQL

SELECT CONCAT( name,
    DATE_FORMAT(birthdt,' was born on  %W')
    ) AS fact
    FROM ppl;

TEXT

+-----------------------------+
| fact                        |
+-----------------------------+
| Paul was born on  Tuesday   |
| Larry was born on  Saturday |
| John was born on  Sunday    |
+-----------------------------+

SQL

SELECT name, DATE_FORMAT(birthdate, '%Y-%m-%d') AS formatted
FROM ppl;

TEXT

+-------+------------+
| name  | formatted  |
+-------+------------+
| Paul  | 1956-11-13 |
| Larry | 1967-12-23 |
| John  | 2019-09-29 |
+-------+------------+

DATEDIFF & DATE_ADD

You can apply math on dates. Reference

DATEDIFF

SQL

SELECT name, birthdate,
    DATEDIFF(NOW(), birthdate) AS "days-old"
FROM ppl;

TEXT

+-------+------------+-----------+
| name  | birthdate  | dayslived |
+-------+------------+-----------+
| Paul  | 1956-11-13 |     22965 |
| Larry | 1967-12-23 |     18908 |
| John  | 2019-09-29 |         0 |
+-------+------------+-----------+

DATE_ADD

SQL

SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH)
AS "Month added",
    DATE_ADD(birthdt, INTERVAL 10 SECOND)
AS "Seconds added"
FROM ppl;

TEXT

+---------------------+---------------------+---------------------+
| birthdt             | Month added         | Seconds added       |
+---------------------+---------------------+---------------------+
| 1956-11-13 10:03:33 | 1956-12-13 10:03:33 | 1956-11-13 10:03:43 |
| 1967-12-23 04:20:47 | 1968-01-23 04:20:47 | 1967-12-23 04:20:57 |
| 2019-09-29 00:44:35 | 2019-10-29 00:44:35 | 2019-09-29 00:44:45 |
+---------------------+---------------------+---------------------+

+ & -

SQL

SELECT birthdt, birthdt - INTERVAL 1 MONTH + INTERVAL 10 YEAR
AS "added dates"
FROM ppl;

TEXT

+---------------------+---------------------+
| birthdt             | added dates         |
+---------------------+---------------------+
| 1956-11-13 10:03:33 | 1966-10-13 10:03:33 |
| 1967-12-23 04:20:47 | 1977-11-23 04:20:47 |
| 2019-09-29 00:44:35 | 2029-08-29 00:44:35 |
+---------------------+---------------------+

TIMESTAMPS

Adding in time stamps in our table--meta data showing when something was created or updated. In MySQL TIMESTAMP is also a data type

DATETIME* and TIMESTAMP both store date and time with two differences in ranges

1000-01-01 00:00:00 to 9999-12-31 23:59:59
1970-01-01 00:00:01 to 2038-01-19 03:14:07

TIMESTAMP exists on top of DATETIME because it takes up less space

In sum, DATETIME covers wider range of dates, but TIMESTAMP takes up less memory.

Example

SQL

CREATE TABLE comments(
    content VARCHAR(100),
    --TIMESTAMP can be replaced with DATETIME but takes MORE space
    created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO comments(content) VALUES ("Lol haha");
INSERT INTO comments(content) VALUES ("I love this post");

TEXT

+------------------+---------------------+
| content          | created_at          |
+------------------+---------------------+
| Lol haha         | 2019-09-29 01:50:40 |
| I love this post | 2019-09-29 01:50:40 |
+------------------+---------------------+

SQL

INSERT INTO comments(content) VALUES ("zjxcn");
SELECT * FROM comments
ORDER BY created_at;

TEXT

+------------------+---------------------+
| content          | created_at          |
+------------------+---------------------+
| Lol haha         | 2019-09-29 01:50:40 |
| I love this post | 2019-09-29 01:50:40 |
| zjxcn            | 2019-09-29 01:52:28 |
+------------------+---------------------+

Orders by time created in ascending order. However, this will show in ascending order by default without ORDER BY because created_at was set with NOW()

ON UPDATE

Not used very commonly but you can do things like

SQL

CREATE TABLE intUp ( changeInt DEFAULT 0 ON UPDATE 3 );

Every time intUP row gets updated, it will change to 3

With date

SQL

CREATE TABLE comments2 (
    content VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW()
    ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO comments2(content) VALUES ("Lol haha");
INSERT INTO comments2(content) VALUES ("I love this post");
INSERT INTO comments2(content) VALUES ("loloolol");

TEXT

+------------------+---------------------+
| content          | changed_at          |
+------------------+---------------------+
| Lol haha         | 2019-09-29 01:58:10 |
| I love this post | 2019-09-29 01:58:20 |
| loloolol         | 2019-09-29 01:59:31 |
+------------------+---------------------+

Try updating

SQL

UPDATE comments2 SET content='Trying to edit comment'
WHERE content="Lol haha";

TEXT

+------------------------+---------------------+
| content                | changed_at          |
+------------------------+---------------------+
| Trying to edit comment | 2019-09-29 02:03:07 |
| I love this post       | 2019-09-29 01:58:20 |
| loloolol               | 2019-09-29 01:59:31 |
+------------------------+---------------------+

Note that changed_at automatically got updated to NOW(). Also it doesn't show in ascending order of time by default anymore

More example

SQL

CREATE TABLE tweets(
    content VARCHAR(100),
    username VARCHAR(30) NOT NULL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO tweets(content, username) VALUES("hahaahhaa", "ooasd");

TEXT

+-----------+----------+---------------------+
| content   | username | created_at          |
+-----------+----------+---------------------+
| hahaahhaa | ooasd    | 2019-09-29 03:33:05 |
+-----------+----------+---------------------+

WRITTEN BY

Ellis Min

Keeping a record

GitHub