search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
chevron_left Date and Time
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook
chevron_left Date and Time
thumb_up
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

DATE, DATETIME and TIMESTAMP

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Data types
chevron_right
Date and Time
schedule Mar 9, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more

DATE

MySQL's DATE type is to be used when we do not need information about the time. It has the following syntax:

'YYYY-MM-DD'
NOTE

The supported range is '1000-01-01' to '9999-12-31'.

We can wrap our dates with single quotes as if they were a string '2016-01-01'. MySQL will automatically parse them as a DATE data type, given that you specified the column data type as DATE when you first created your table.

To create a table called products with a DATE type column created_at:

CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(30) NOT NULL, price INT NOT NULL,
created_at DATE NOT NULL,
PRIMARY KEY (id)
);

To insert two values into the products table:

INSERT INTO products (name, price, created_at)
VALUES ('apple', 20, '2018-01-01'), ('banana', 30, '2016-01-01');
SELECT * FROM products;
+----+--------+-------+------------+
| id | name | price | created_at |
+----+--------+-------+------------+
| 1 | apple | 20 | 2018-01-01 |
| 2 | banana | 30 | 2016-01-01 |
+----+--------+-------+------------+

DATETIME

MySQL's DATETIME type is used for values with both date and time parts. It has the following syntax:

'YYYY-MM-DD hh:mm:ss'
NOTE

The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

You can learn more about the differences between DATETIME and TIMESTAMP here.

TIMESTAMP

MySQL's TIMESTAMP type is used for values with both date and time parts. It has the following syntax:

'YYYY-MM-DD hh:mm:ss'

When you insert a TIMESTAMP value into a table, MySQL automatically converts it to UTC time and stores it. When you retrieve the TIMESTAMP it is automatically converted back to your connection time zone.

NOTE

The supported range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

You can learn more about the differences between DATETIME and TIMESTAMP here.

Fractional seconds

DATETIME and TIMESTAMP values can store a fractional seconds portion for microseconds (up to 6 digits precision):

'hh:mm:ss.ffffff'

Note that the delimiter between seconds and microseconds must be a dot (.)

For the fractional seconds to be stored, the column must be defined to accept fractional seconds up to specific precision. The general syntax is as follows:

column_name type_name(fsp);

To create a table with columns that can hold fractional seconds up to a specified precision:

CREATE TABLE sample_table (column1 TIME(3), column2 DATETIME(6), column3 TIMESTAMP(1));
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...