业务代码中常用的，Java&MySQL的日期和时间

MYSQL

mysql的时间日期存储格式

日期类型      表示         存储空间      日期格式                日期范围
----------  ----------   -------  --------------------- -----------------------------------------
datetime    年月日时分秒    8 bytes  	 YYYY-MM-DD HH:MM:SS  	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp   年月日时分秒    4 bytes    YYYY-MM-DD HH:MM:SS  	1970-01-01 00:00:01 ~ 2038
date        年月日         3 bytes    YYYY-MM-DD             1000-01-01          ~ 9999-12-31
time		时分秒 		3 bytes	   HH:MM:SS                -838:59:59          ~ 838:59:59
year        年            1 bytes    YYYY                    1901                ~ 2155

一、MySQL 获得当前日期、时间函数

`now()` 、`CURRENT_TIMESTAMP`获取当前年月日、时分秒的函数

#sql语句中使用now()获取当前时间，也可通过DATE_FORMAT函数限制天数，作为where条件
mysql> select now();
+---------------------+
| now()              |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+
mysql>  xxxx xxxx where  DATE_FORMAT (t.lottery_draw ,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d')

#建表时可以使用 `CURRENT_TIMESTAMP` 简化操作
CREATE TABLE `t_market_activity_prize_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   ....
   ....
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;

`utc_date()、 utc_time()、 utc_timestamp()` 获得当前 UTC 日期时间函数

mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+---------------------+
| utc_timestamp()    | utc_date() | utc_time() | now()              |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11  | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+

因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候，非常有用。

`curdate()`获取当前日期的函数

mysql> select curdate();

+------------+
| curdate()  |
+------------+
| 2008-08-08 |
+------------+

`curtime()`获得当前时间的函数

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 22:41:30  |
+-----------+

二、MySQL 日期、时间选取函数

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2008-09-10 07:15:30.123456';

select date(@dt);        -- 2008-09-10
select time(@dt);        -- 07:15:30.123456
select year(@dt);        -- 2008
select quarter(@dt);    -- 3
select month(@dt);      -- 9
select week(@dt);        -- 36
select day(@dt);        -- 10
select hour(@dt);        -- 7
select minute(@dt);      -- 15
select second(@dt);      -- 30
select microsecond(@dt); -- 123456

`Extract()` 函数，选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2008-09-10 07:15:30.123456';

select extract(year                from @dt); -- 2008
select extract(quarter            from @dt); -- 3
select extract(month              from @dt); -- 9
select extract(week                from @dt); -- 36
select extract(day                from @dt); -- 10
select extract(hour                from @dt); -- 7
select extract(minute              from @dt); -- 15
select extract(second              from @dt); -- 30
select extract(microsecond        from @dt); -- 123456

select extract(year_month          from @dt); -- 200809
select extract(day_hour            from @dt); -- 1007
select extract(day_minute          from @dt); -- 100715
select extract(day_second          from @dt); -- 10071530
select extract(day_microsecond    from @dt); -- 10071530123456
select extract(hour_minute        from @dt); --    715
select extract(hour_second        from @dt); --    71530
select extract(hour_microsecond    from @dt); --    71530123456
select extract(minute_second      from @dt); --      1530
select extract(minute_microsecond  from @dt); --      1530123456
select extract(second_microsecond  from @dt); --        30123456

*MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’ 等功能。注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。

`last_day()` 函数：返回月份中的最后一天

select last_day('2008-02-01');  -- 2008-02-29
select last_day('2008-08-08');  -- 2008-08-31

mysql> select now(), day(last_day(now())) as days;
+---------------------+------+
| now()              | days |
+---------------------+------+
| 2008-08-09 11:45:45 |  31 |
+---------------------+------+

三、MySQL 日期、时间计算函数

`DATEDIFF（,）`获取两个日期的时间间隔

date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。

mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
    -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
|    1 |  -15 |
+------+------+
1 row in set (0.00 sec)

`TIMEDIFF( , )`日期时间相减函数

select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00');  --  743:59:59
select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59');  -- -743:59:59
select timediff('23:59:59', '12:00:00');                        --  11:59:59

*timediff 的两个参数只能是 datetime/timestamp, time 类型的，并且这两个参数类型要相同。即：datetime/timestamp 和 datetime/timestamp 比较；time 和 time 相比较。

TIMESTAMPDIFF(unit,expr1,expr2) --以unit为单位，比较时间差

select timestampdiff(year,'2002-05-01','2001-01-01');                    -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01');                    -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00');  -- -12

四、MySQL 日期、时间转换函数

`str_to_date(, )`字符串转换为日期、时间函数

str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。

select str_to_date('08/09/2008', '%m/%d/%Y');                  -- 2008-08-09
select str_to_date('08/09/08'  , '%m/%d/%y');                  -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y');                  -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s');                    -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

`DATE_FORMAT(,)` 、`TIME_FORMAT(,)` 日期、时间转换为字符串函数

date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的一个逆转换。

mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');
+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Friday August 2008                            |
+------------------------------------------------+

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301                                    |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');
+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01                            |
+-------------------------------------+


#按年月日 时分秒匹配
SELECT * FROM t_market_activity_prize_record where DATE_FORMAT(lottery_draw,'%Y-%m-%d %H:%i:%s')  =  DATE_FORMAT('2021-07-13 11:39:57','%Y-%m-%d %H:%i:%s')	

#按年月日匹配
SELECT * FROM t_market_activity_prize_record where DATE_FORMAT(lottery_draw,'%Y-%m-%d')  =  DATE_FORMAT('2021-07-13','%Y-%m-%d')

五、MySQL 时间戳（Timestamp）函数

`current_timestamp` `current_timestamp()` 获得当前时间戳函数

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp  | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

时间戳（timestamp）转换、增、减函数

timestamp(date)                        -- date to timestamp
timestamp(dt,time)                      -- dt + time
timestampadd(unit,interval,datetime_expr)      

select timestamp('2008-08-08');                        -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01');    -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00');    -- 2008-08-09 08:00:00

JAVA

java中的时间日期表示:

java.util.Date 格式：Thu Jul 15 17:35:34 CST 2021
java.sql.Date 只读取日期
java.sql.Time 只读取时间
java.sql.Timestamp 读取日期和时间

后面三个类继承自 java.util.Date 扩展了其功能。

数据库使用date, java实体类推荐使用java.sql.Date接收
数据库使用time, java实体类推荐使用java.sql.Time接收
数据库使用datetime, java实体类推荐使用String接收
数据库使用timestamp, java实体类推荐使用java.util.Date接收

//1. 将日期转化为特定格式的字符串
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
string timeString = sdf.format(new Date())
//2. 将特定格式的字符串转化为日期(注意:格式需要匹配)
String strDate1 = "2018年06月03日  13时14分21秒"; 
DateFormat format1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
Date date1 = format1.parse(strDate1);

//date转string
private static String formatDate1(Date date) {
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    return format.format(date);
}
//string转date
private static Date formatDate2(String strDate){
    Date date = DateUtil.parse(strDate, "yyyy-MM-dd HH:mm:ss");
    return date;
}





注意：
    SimpleDateFormat 线程不安全！
    当我们使用SimpleDataFormat的parse()方法，内部有一个Calendar对象，调用SimpleDataFormat的parse()方法会先调用Calendar.clear（），然后调用Calendar.add()，如果一个线程先调用了add()然后另一个线程又调用了clear()，这时候parse()方法解析的时间就不对了。

    解决办法：要么每次都new一个，要么加锁同步
    方法一：
    public static String formatDate1(Date date) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        return format.format(date);
    }

    方法二： 
    private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    public static synchronized String formatDate2(Date date) {
        return formatter.format(date);
    }

posted @ 2021-07-20 11:16 liangsiyuan 阅读(446) 评论(0) 收藏举报

刷新页面返回顶部

liangsiyuan

业务代码中常用的，Java&MySQL的日期和时间

MYSQL

mysql的时间日期存储格式

一、MySQL 获得当前日期、时间函数

now() 、CURRENT_TIMESTAMP获取当前 年月日、时分秒 的函数

utc_date()、 utc_time()、 utc_timestamp() 获得当前 UTC 日期时间函数

curdate()获取当前日期的函数

curtime()获得当前时间的函数