Common statements of MySQL date

Time:2021-1-13

data type

  1. datetime
  2. timestamp

Statement testing

  1. Table building

    DROP TABLE IF EXISTS test_time;
    CREATE TABLE test_time (
     `id` INT (3) UNSIGNED AUTO_INCREMENT,
     `Name ` char (5) default '// used for update time test
     `test_datetime` datetime DEFAULT CURRENT_TIMESTAMP,
     `test_datetime1` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     `test_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `test_timestamp1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     `int_timestamp` INT NOT NULL DEFAULT 1577808000,
     PRIMARY KEY (`id`)
    );
  2. insert data

    INSERT into test_time(name) VALUE('huang');

    Because default current has been used in table creation_ Therefore, the current time will be created automatically after inserting data

    UPDATE test_time set name='hui' WHERE name='huang';

    Test_ datetime1,test_ Timestamp1 can automatically update the time;

  1. Common function test SQL
    SELECT 
    test_datetime,
     DATE_FORMAT(test_datetime,'%Y-%m-%d'),
     FROM_UNIXTIME(int_timestamp) as int_day,
     YEAR(test_datetime) as year,
     MONTH(test_datetime) as month,
     DAY(test_datetime) as day,
     HOUR(test_datetime) as hour,
     minute(test_datetime) as minute,
     second(test_datetime) as second,
     MICROSECOND(test_datetime) as mic_second,
     WEEK(test_datetime) as week,
     QUARTER(test_datetime) as quarter,
     MONTHNAME(test_datetime) as month_name,
     NOW() as now,
     CURRENT_date() as cur_date,
     CURRENT_TIME() as cur_time,
     DATE_ADD(NOW(),INTERVAL 1 DAY) as add_day,
     DATE_SUB(NOW(),INTERVAL -1 DAY) as sub_day,
     DATEDIFF(NOW(),FROM_UNIXTIME(int_timestamp)) as diff
    FROM test_time;
  1. Function interpretation
function Parameter type explain
FROM_UNIXTIME timestamp Timestamp – > date format
UNIX_TIMESTAMP datetime Date format – > timestamp
DATE_FORMAT (datetime , ‘%Y-%m-%d,%H:%i:%s’) Beautify date format
YEAR datetime Gets the date formatyear
MONTH datetime Gets the date formatmonth
DAY datetime Gets the date formatdate
HOUR datetime Gets the date formathour
MINUTE datetime Gets the date formatminute
SECOND datetime Gets the date formatsecond
MICROSECOND datetime Gets the date formatMicrosecond
WEEK datetime Gets the date formatWhat week
QUARTER datetime Gets the date formatquarter
MONTHNAME datetime Gets the date formatMonth
NOW Get the current time2020-12-12 12:12:12
CURRENT_DATE Get current date2020-12-12
CURRENT_TIME Gets the current time, minute and second12:12:12
DATE_ADD (datetime,INTERVAL expr unit) The number of days was increasedDATE_ADD(timestamp,INTERVAL 1 DAY)
DATE_SUB (datetime,INTERVAL expr unit) Reduce N days
DATEDIFF (datetime1,datetime2) How many days is the interval between the two

-Above date_ ADD/DATE_ In subunitThe parameters can be as follows:

year month day Time branch second millisecond week quarter
YEAR MONTH DAY HOUR MINTUE SECOND MIROSECOND WEEK QUATER

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]