MySQL的日期时间类型-平芜编程栈

一 MySQL的日期时间类型

MySQL数据库的日期时间类型有date、time和datetime类型，还有timestamp类型，在Java代码中无论日期时间是什么样的格式，转换sql语句时统一为yyyy-MM-dd HH:mm:ss.S(Timestamp)的格式，创建案例简单演示。

1.1 创建数据库表

CREATE TABLE `apple` ( `id` varchar(255) DEFAULT NULL, `date_variable` date DEFAULT NULL, `time_variable` time DEFAULT NULL, `datetime_variable` datetime DEFAULT NULL )

1.2 创建apple表的基础代码

@Data @AllArgsConstructor @NoArgsConstructor @TableName("apple") public class Apple { private String id; private Date dateVariable; private Date timeVariable; private Date datetimeVariable; } @Mapper public interface AppleMapper extends BaseMapper<Apple> { } public interface IAppleService extends IService<Apple> { } @Service public class AppleServiceImpl extends ServiceImpl<AppleMapper, Apple> implements IAppleService { }

1.3 插入日期时间数据

@SpringBootTest @RunWith(SpringRunner.class) public class AppleTest { @Autowired private IAppleService appleService; @Test public void test1() throws ParseException { Apple apple = new Apple(); apple.setId("1001"); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date = simpleDateFormat.parse("2024-01-01 01:01:01"); apple.setDatetimeVariable(date); appleService.save(apple); } }

当代码中的日期时间格式是yyyy-MM-dd HH:mm:ss，没有指定毫秒值，sql语句解析如下：
==> Preparing: INSERT INTO apple ( id, datetime_variable ) VALUES ( , )
==> Parameters: 1001(String), 2024-01-01 01:01:01.0(Timestamp)
<== Updates: 1

@SpringBootTest @RunWith(SpringRunner.class) public class AppleTest { @Autowired private IAppleService appleService; @Test public void test1() throws ParseException { Apple apple = new Apple(); apple.setId("1002"); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); Date date = simpleDateFormat.parse("2024年01月02日 01时01分01秒"); apple.setDatetimeVariable(date); appleService.save(apple); } }

当代码中的日期时间格式是yyyy年MM月dd日 HH时mm分ss秒，没有指定毫秒值，sql语句解析如下：
==> Preparing: INSERT INTO apple ( id, datetime_variable ) VALUES ( , )
==> Parameters: 1002(String), 2024-01-02 01:01:01.0(Timestamp)
<== Updates: 1
在Java代码中无论日期时间(不指定毫秒值)是什么样的格式，转换sql语句时统一为yyyy-MM-dd HH:mm:ss.S(Timestamp)的格式

1.4 MySQL的date类型

@Test public void test1() throws ParseException { List<Apple> list = new ArrayList<>(); Apple apple = new Apple(); apple.setId("1001"); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date = simpleDateFormat.parse("2023-12-06 17:59:27"); apple.setDateVariable(date); Apple apple2 = new Apple(); apple2.setId("1002"); SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy-MM-dd"); Date date2 = simpleDateFormat2.parse("2023-12-06"); apple2.setDateVariable(date2); Apple apple3 = new Apple(); apple3.setId("1003"); SimpleDateFormat simpleDateFormat3 = new SimpleDateFormat("yyyy"); Date date3 = simpleDateFormat3.parse("2023"); apple3.setDateVariable(date3); list.add(apple); list.add(apple2); list.add(apple3); appleService.saveBatch(list); }

在Java代码中无论日期时间(不指定毫秒值)是什么样的格式，转换sql语句时统一为yyyy-MM-dd HH:mm:ss.S(Timestamp)的格式，代码中的sql语句解析如下：
==> Preparing: INSERT INTO apple ( id, date_variable ) VALUES ( , )
==> Parameters: 1001(String), 2023-12-06 17:59:27.0(Timestamp)
==> Parameters: 1002(String), 2023-12-06 00:00:00.0(Timestamp)
==> Parameters: 1003(String), 2023-01-01 00:00:00.0(Timestamp)

apple表中的date_variable字段是date类型，数据库表存储date类型的格式是yyyy-MM-dd, 多余的日期时间数据会截取掉，缺省的日期默认取01月01日，上述三条记录入表如下：

1.5MySQL的time类型

@Test public void test1() throws ParseException { List<Apple> list = new ArrayList<>(); Apple apple4 = new Apple(); apple4.setId("1004"); SimpleDateFormat simpleDateFormat4 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); Date date4 = simpleDateFormat4.parse("2023-12-06 17:59:27.123"); apple4.setTimeVariable(date4); Apple apple5 = new Apple(); apple5.setId("1005"); SimpleDateFormat simpleDateFormat5 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date5 = simpleDateFormat5.parse("2023-12-06 17:59:27"); apple5.setTimeVariable(date5); Apple apple6 = new Apple(); apple6.setId("1006"); SimpleDateFormat simpleDateFormat6 = new SimpleDateFormat("yyyy-MM-dd HH"); Date date6 = simpleDateFormat6.parse("2023-12-06 17"); apple6.setTimeVariable(date6); Apple apple7 = new Apple(); apple7.setId("1007"); SimpleDateFormat simpleDateFormat7 = new SimpleDateFormat("yyyy-MM-dd"); Date date7 = simpleDateFormat7.parse("2023-12-06"); apple7.setTimeVariable(date7); list.add(apple4); list.add(apple5); list.add(apple6); list.add(apple7); appleService.saveBatch(list); }

在Java代码中无论日期时间(不指定毫秒值)是什么样的格式，转换sql语句时统一为yyyy-MM-dd HH:mm:ss.S(Timestamp)的格式，如果日期时间格式带有毫秒值，那么转换sql语句时将带有对应的毫秒值，代码中的sql语句解析如下：
==> Preparing: INSERT INTO apple ( id, time_variable ) VALUES ( , )
==> Parameters: 1004(String), 2023-12-06 17:59:27.123(Timestamp)
==> Parameters: 1005(String), 2023-12-06 17:59:27.0(Timestamp)
==> Parameters: 1006(String), 2023-12-06 17:00:00.0(Timestamp)
==> Parameters: 1007(String), 2023-12-06 00:00:00.0(Timestamp)

apple表中的time_variable字段是time类型，数据库表存储time类型的格式是HH:mm:ss, 多余的日期时间数据会截取掉，缺省的时间默认取00时00分00秒，上述四条记录入表如下：

1.6MySQL的datetime类型

@Test public void test1() throws ParseException { List<Apple> list = new ArrayList<>(); Apple apple8 = new Apple(); apple8.setId("1008"); SimpleDateFormat simpleDateFormat8 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); Date date8 = simpleDateFormat8.parse("2023-12-06 17:59:27.123"); apple8.setDatetimeVariable(date8); Apple apple9 = new Apple(); apple9.setId("1009"); SimpleDateFormat simpleDateFormat9 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date9 = simpleDateFormat9.parse("2023-12-06 17:59:27"); apple9.setDatetimeVariable(date9); Apple apple10 = new Apple(); apple10.setId("10010"); SimpleDateFormat simpleDateFormat10 = new SimpleDateFormat("yyyy-MM-dd HH"); Date date10 = simpleDateFormat10.parse("2023-12-06 17"); apple10.setDatetimeVariable(date10); Apple apple11 = new Apple(); apple11.setId("10011"); SimpleDateFormat simpleDateFormat11 = new SimpleDateFormat("yyyy-MM-dd"); Date date11 = simpleDateFormat11.parse("2023-12-06"); apple11.setDatetimeVariable(date11); Apple apple12 = new Apple(); apple12.setId("10012"); SimpleDateFormat simpleDateFormat12 = new SimpleDateFormat("yyyy"); Date date12 = simpleDateFormat12.parse("2023"); apple12.setDatetimeVariable(date12); list.add(apple8); list.add(apple9); list.add(apple10); list.add(apple11); list.add(apple12); appleService.saveBatch(list); }

在Java代码中无论日期时间(不指定毫秒值)是什么样的格式，转换sql语句时统一为yyyy-MM-dd HH:mm:ss.S(Timestamp)的格式，如果日期时间格式带有毫秒值，那么转换sql语句时将带有对应的毫秒值，代码中的sql语句解析如下：
==> Preparing: INSERT INTO apple ( id, datetime_variable ) VALUES ( , )
==> Parameters: 1008(String), 2023-12-06 17:59:27.123(Timestamp)
==> Parameters: 1009(String), 2023-12-06 17:59:27.0(Timestamp)
==> Parameters: 10010(String), 2023-12-06 17:00:00.0(Timestamp)
==> Parameters: 10011(String), 2023-12-06 00:00:00.0(Timestamp)
==> Parameters: 10012(String), 2023-01-01 00:00:00.0(Timestamp)

apple表中的datetime_variable字段是datetime类型，数据库表存储datetime类型的格式是yyyy-MM-dd HH:mm:ss, 多余的日期时间数据会截取掉，缺省的日期默认取01月01日 00时00分00秒，上述五条记录入表如下：

二 MySQL的datetime和timestamp

MySQL数据库的datetime和timestamp类型比较：
datetime类型需要8个字节的存储空间，timestamp类型需要4个字节的存储空间；
datetime类型的取值范围1001-01-01 00:00:00到9999-12-31 23:59:59；
timestamp类型的取值范围1970-01-01 00:00:00到2037-12-31 23:59:59 utc世界统一时间；
datetime类型存储的是本地时区(东八区)的日期时间，其他时区的用户查看数据也是东八区的日期时间，存在必然的误差，datetime类型存储数据基本上是原样输入和输出；
timestamp类型存储的是毫秒值，当前时间距1970-01-01 00:00:00的毫秒值，存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换为当前的时区，so使用timestamp类型存储的同一个时间值，在不同的时区查询时会显示不同的时间；
一般地，MySQL数据库存储日期时间使用datetime类型，用于日期时间函数计算使用timestamp类型，还有跨时区的业务用timestamp类型更合适；