mysql中的日期函数

---

---

MySql中的日期函数

当前日期

SELECT
    CURDATE( ) AS curDate
    
-- 等价表示
SELECT
    CURRENT_DATE( ) AS curDate

明天日期

SELECT
    CURDATE( ) AS curDate,
    DATE_SUB( CURDATE( ), INTERVAL - 1 DAY ) AS tomorrow

昨天日期

SELECT
    CURDATE( ) AS curDate,
    DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) AS yesterday

将日期转化为其所在周的第1天

SELECT
    CURDATE( ) AS curDate,
    DATE_ADD(CURDATE( ), INTERVAL - WEEKDAY( CURDATE( ) ) DAY) AS firstDayOfWeek

将日期转化为其所在周的最后1天

SELECT
    CURDATE( ) AS curDate,
    DATE_ADD(CURDATE( ), INTERVAL 6 - WEEKDAY( CURDATE( ) ) DAY) AS lastDayOfWeek

将日期转化为其所在月的第1天

SELECT
    CURDATE( ) AS curDate,
    DATE_SUB(CURDATE( ), INTERVAL DAY ( CURDATE( ) ) - 1 DAY) AS firstDayOfMonth

-- 等价于
SELECT
    CURDATE( ) AS curDate,
    DATE_SUB(CURDATE( ), INTERVAL DAYOFMONTH ( CURDATE( ) ) - 1 DAY) AS firstDayOfMonth    

将日期转化为其所在月的最后1天

SELECT
    CURDATE( ) AS curDate,
    LAST_DAY( CURDATE( ) ) AS lastDayOfMonth

将日期转化为其所在年的第1天

SELECT
    CURDATE( ) AS curDate,
    DATE_SUB(CURDATE( ), INTERVAL DAYOFYEAR ( CURDATE( ) ) - 1 DAY) AS firstDayOfYear

将日期转化为其所在年的最后1天

SELECT
    CURDATE( ) AS curDate,
    LAST_DAY(DATE_ADD(NOW( ), INTERVAL 12- MONTH ( NOW( ) ) MONTH)) AS lastDayOfYear

mysql自动维护与记录相关的时间SQL

ALTER TABLE table_name 
ADD COLUMN create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
ADD COLUMN update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';

Java8中的日期函数

Date --> LocalDate

// Sat Apr 18 09:30:40 CST 2020
Date date = new Date();

// 2020-04-18
LocalDate localDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault()).toLocalDate();

LocalDate --> Date

// 2020-04-18
LocalDate localDate = LocalDate.now();

// Sat Apr 18 00:00:00 CST 2020
Date date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());

日期所在周的第1天

// 2020-04-18
LocalDate localDate = LocalDate.now();

// 2020-04-13
LocalDate firstDateOfWeek = localDate.with(DayOfWeek.MONDAY);

日期所在周的最后1天

// 2020-04-18
LocalDate localDate = LocalDate.now();

// 2020-04-19
LocalDate lateDateOfWeek = localDate.with(DayOfWeek.SUNDAY);

日期所在月的第一天

// 2020-04-18
LocalDate localDate = LocalDate.now();

// 2020-04-01
LocalDate firstDateOfMonth = localDate.withDayOfMonth(1);

日期所在月的最后1天

// 2020-04-18
LocalDate localDate = LocalDate.now();

// 2020-04-30
LocalDate lastDateOfMonth = localDate.with(TemporalAdjusters.lastDayOfMonth());

日期所在年的第1天

// 2020-04-18
LocalDate localDate = LocalDate.now();

// 2020-01-01
LocalDate firstDateOfYear = localDate.with(TemporalAdjusters.firstDayOfYear());

日期所在年的最后1天

// 2020-04-18
LocalDate localDate = LocalDate.now();

// 2020-12-31
LocalDate lastDateOfYear = localDate.with(TemporalAdjusters.lastDayOfYear());

日期运算

## +1天
localDate.plusDays(1);

## +1周
localDate.plusWeeks(1);

## +1月
localDate.plusMonths(1);

参考链接

• MySQL 日期与时间方面的函数