In today's day and age, you want to return data in formats that are web compatible. For example, when you want to pass a date to your front end JavaScript, you don't want to pass 2019-12-21 13:31:39. It's not parsable. Meaning, JavaScript just sees this as a string. Also, most people in America will hate the way this looks. Also, you probably don't want to include the seconds, or even the time entirely. We have these awesome date/time functions you can use to customize the date and the time output to look the way we want it to look. In fact, JavaScript has built in functions that will make a date and time output in the users country format, automatically.

We just need to pass a standardized unit of time. That unit of time is called the Unix Epoch Time. Basically, when Unix was brought online for the first time, is 0. The Unix Epoch goes up one digit per second.

You can get the current Unix Time from your Unix Terminal by typing:

date +%s
1576964524 - Unix Time

Sat 21 Dec 2019 01:42:01 PM PST - Regular time

So, how do we get Unix Time from MySQL? We use a MySQL function.

mysql> select unix_timestamp(started) as started, lastName from names;
+------------+----------+
| started    | lastName |
+------------+----------+
| 1546329600 | Smith    |
| 1546329600 | Rocker   |
| 1576915200 | Kella    |
| 1576963899 | Toga     |
+------------+----------+
4 rows in set (0.00 sec)

We added the MySQL function unix_timestamp(). It takes one parameter, which we fill with the field. The function converts what's in the field to a unix timestamp and outputs that number instead of a regular MySQL datetime.

You'll also notice that we also used a keyword, as. We basically told mysql to output the field as something else aka renamed the field name on the fly. What happens when we do the above select without the as?

mysql> select unix_timestamp(started), lastName from names;
+-------------------------+----------+
| unix_timestamp(started) | lastName |
+-------------------------+----------+
|              1546329600 | Smith    |
|              1546329600 | Rocker   |
|              1576915200 | Kella    |
|              1576963899 | Toga     |
+-------------------------+----------+
4 rows in set (0.00 sec)

The column turns out totally different. In PHP, we wouldn't be able to access the column as started. We'd have to access the column as unix_timestamp(started), which is kinda lame.

And just to make it fun, go ahead and open up your chrome console (F12) and let's do a date formatting in JavaScript.

chrome> new Date(1576964524*1000).toLocaleString()
"12/21/2019, 1:42:04 PM"

We multiply by 1000 because JavaScript likes Unix Timestamps to be in milliseconds whereas MySQL is giving us Timestamps in seconds.