untung99play.xyz: A Complete Guide to MySQL DATETIME Data Type

Untung99 menawarkan beragam permainan yang menarik, termasuk slot online, poker, roulette, blackjack, dan taruhan olahraga langsung. Dengan koleksi permainan yang lengkap dan terus diperbarui, pemain memiliki banyak pilihan untuk menjaga kegembiraan mereka. Selain itu, Untung99 juga menyediakan bonus dan promosi menarik yang meningkatkan peluang kemenangan dan memberikan nilai tambah kepada pemain.

Berikut adalah artikel atau berita tentang Harian untung99play.xyz dengan judul untung99play.xyz: A Complete Guide to MySQL DATETIME Data Type yang telah tayang di untung99play.xyz terimakasih telah menyimak. Bila ada masukan atau komplain mengenai artikel berikut silahkan hubungi email kami di koresponden@untung99play.xyz, Terimakasih.

Summary: in this tutorial, you will learn about MySQL DATETIME data type and how to use some handy functions for manipulating DATETIME effectively.

Introduction to MySQL DATETIME data type

You use MySQL DATETIME to store a value that contains both date and time. When you query data from a DATETIME column, MySQL displays the DATETIME value in the following format:

YYYY-MM-DD HH:MM:SSCode language: SQL (Structured Query Language) (sql)

By default, DATETIME values range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

A DATETIME value uses 5 bytes for storage. In addition, a DATETIME value can include a trailing fractional second up to microseconds with the format YYYY-MM-DD HH:MM:SS[.fraction] e.g., 2015-12-20 10:01:00.999999. When including the fractional second precision, DATETIME values require more storage as illustrated in the following table:

Fractional Seconds Precision	Storage (Bytes)
0	0
1, 2	1
3, 4	2
5, 6	3

For example, 2015-12-20 10:01:00.999999 requires 8 bytes, 5  bytes for 2015-12-20 10:01:00 and 3 bytes for .999999 while 2015-12-20 10:01:00.9  requires only 6 bytes, 1 byte for the fractional second precision.

Note that before MySQL 5.6.4, DATETIME values requires 8 bytes storage instead of 5 bytes.

MySQL DATETIME vs. TIMESTAMP

MySQL provides another temporal data type that is similar to the DATETIME called  TIMESTAMP.

The TIMESTAMP requires 4 bytes while DATETIME requires 5 bytes. Both TIMESTAMP and DATETIME require additional bytes for fractional seconds precision.

TIMESTAMP values range from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. If you want to store temporal values that are beyond 2038, you should use DATETIME instead of TIMESTAMP.

MySQL stores TIMESTAMP in UTC value. However, MySQL stores the DATETIME value as is without timezone. Let’s see the following example.

First, set the timezone of the current connection to +00:00.

SET time_zone = '+00:00';Code language: SQL (Structured Query Language) (sql)

Next, create a table named timestamp_n_datetime that consists of two columns: ts and dt with TIMESTAMP and DATETIME types using the following statement.

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);Code language: SQL (Structured Query Language) (sql)

Then, insert the current date and time into both ts and dt columns of the timestamp_n_datetime table,

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());Code language: SQL (Structured Query Language) (sql)

After that, query data from the timestamp_n_datetime table.

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;Code language: SQL (Structured Query Language) (sql)

Both values in DATETIME and TIMESTAMP columns are the same.

Finally, set the connection’s time zone to +03:00 and query data from the timestamp_n_datetime table again.

SET time_zone = '+03:00';

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;Code language: SQL (Structured Query Language) (sql)

As you can see, the value in the TIMESTAMP column is different. This is because the TIMESTAMP column stores the date and time value in UTC when we changed the time zone, the value of the TIMESTAMP column is adjusted according to the new time zone.

It means that if you use the TIMESTAMP data to store date and time values, you should take a serious consideration when you move your database to a server located in a different time zone.

MySQL DATETIME functions

The following statement sets the variable @dt to the current date and time using the NOW() function.

SET @dt =  NOW();Code language: SQL (Structured Query Language) (sql)

To query the value of the @dt variable, you use the following SELECT statement:

SELECT @dt;Code language: SQL (Structured Query Language) (sql)

MySQL DATE function

To extract the date portion from a DATETIME value, you use the DATE function as follows:

SELECT DATE(@dt);Code language: SQL (Structured Query Language) (sql)

This function is very useful in case you want to query data based on a date but the data stored in the column is based on both date and time.

Let’s see the following example.

CREATE TABLE test_dt (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME
);

INSERT INTO test_dt(created_at)
VALUES('2015-11-05 14:29:36');Code language: SQL (Structured Query Language) (sql)

Suppose you want to know which row created on 2015-11-05, you use the following query:

SELECT 
    *
FROM
    test_dt
WHERE
    created_at = '2015-11-05';Code language: SQL (Structured Query Language) (sql)

It returns no rows.

This is because the created_at column contains not only date but also time. To correct it, you use the DATE function as follows:

SELECT 
    *
FROM
    test_dt
WHERE
    DATE(created_at) = '2015-11-05';Code language: SQL (Structured Query Language) (sql)

It returns one row as expected. In case the table has many rows, MySQL has to perform a full table scan to locate the rows that match the condition.

MySQL TIME function

To extract the time portion from a DATETIME value, you use the TIME function as the following statement:

SELECT TIME(@dt);Code language: SQL (Structured Query Language) (sql)

MySQL YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE and SECOND functions

To get the year, quarter, month, week, day, hour, minute, and second from a DATETIME value, you use the functions as shown in the following statement:

SELECT 
    HOUR(@dt),
    MINUTE(@dt),
    SECOND(@dt),
    DAY(@dt),
    WEEK(@dt),
    MONTH(@dt),
    QUARTER(@dt),
    YEAR(@dt);Code language: SQL (Structured Query Language) (sql)

MySQL DATE_FORMAT function

To format a DATETIME value, you use the DATE_FORMAT function. For example, the following statement formats a DATETIME value based on the %H:%i:%s - %W %M %Y format:

SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');Code language: SQL (Structured Query Language) (sql)

MySQL DATE_ADD function

To add an interval to a DATETIME value, you use DATE_ADD function as follows:

SELECT @dt start, 
       DATE_ADD(@dt, INTERVAL 1 SECOND) '1 second later',
       DATE_ADD(@dt, INTERVAL 1 MINUTE) '1 minute later',
       DATE_ADD(@dt, INTERVAL 1 HOUR) '1 hour later',
       DATE_ADD(@dt, INTERVAL 1 DAY) '1 day later',
       DATE_ADD(@dt, INTERVAL 1 WEEK) '1 week later',
       DATE_ADD(@dt, INTERVAL 1 MONTH) '1 month later',
       DATE_ADD(@dt, INTERVAL 1 YEAR) '1 year later';Code language: SQL (Structured Query Language) (sql)

MySQL DATE_SUB function

To subtract an interval from a DATETIME value, you use DATE_SUB function as follows:

SELECT @dt start, 
       DATE_SUB(@dt, INTERVAL 1 SECOND) '1 second before',
       DATE_SUB(@dt, INTERVAL 1 MINUTE) '1 minute before',
       DATE_SUB(@dt, INTERVAL 1 HOUR) '1 hour before',
       DATE_SUB(@dt, INTERVAL 1 DAY) '1 day before',
       DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before',
       DATE_SUB(@dt, INTERVAL 1 MONTH) '1 month before',
       DATE_SUB(@dt, INTERVAL 1 YEAR) '1 year before';Code language: SQL (Structured Query Language) (sql)

MySQL DATE_DIFF function

To calculate a difference in days between two DATETIME values, you use the DATEDIFF function. Notice that the DATEDIFF function only considers the date part of a DATETIME value in the calculation.

See the following example.

First, create a table named datediff_test that has one column whose data type is DATETIME.

CREATE TABLE datediff_test (
    dt DATETIME
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the datediff_test table.

INSERT INTO datediff_test(dt)
VALUES('2010-04-30 07:27:39'),
	('2010-05-17 22:52:21'),
	('2010-05-18 01:19:10'),
	('2010-05-22 14:17:16'),
	('2010-05-26 03:26:56'),
	('2010-06-10 04:44:38'),
	('2010-06-13 13:55:53');Code language: SQL (Structured Query Language) (sql)

Third, use the DATEDIFF function to compare the current date and time with the value in each row of the datediff_test table.

SELECT 
    dt, 
    DATEDIFF(NOW(), dt)
FROM
    datediff_test;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about MySQL DATETIME data type and some useful DATETIME functions.