CCNAIVE

1 MySQL截取日期

2023/8/30

# 1 MySQL截取日期

SELECT DATE_FORMAT(your_datetime_column, '%m-%d') AS extracted_date
FROM your_table;

# 2 截取天

SELECT TIME_FORMAT(your_time_column, '%H:%i') AS extracted_time
FROM your_table;

# 3 最近一小时

create_time >= NOW() - INTERVAL 24 HOUR

# 4 查询最近7天，一个月，一年的数据

select * from bd_disaster_warning  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(format_time);
select * from bd_disaster_warning  where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(format_time);
select * from bd_disaster_warning  where DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(format_time);

# 5 时间格式化

DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS create_time

# 6 MySQL默认时间

CURRENT_TIMESTAMP

# 7 最新时间

SELECT crc.*
FROM camera_result_count crc
         INNER JOIN (
    SELECT index_code, MAX(create_time) AS maxTime
    FROM camera_result_count
    GROUP BY index_code
) maxTimes ON crc.index_code = maxTimes.index_code AND crc.create_time = maxTimes.maxTime;

# 8 now函数

SELECT NOW(),CURDATE(),CURTIME()

MySQL-多数据源配置