mysql中date_format函数格式化日期,如何精确到毫秒?

2023-11-16 08:56:41 浏览数 (2)

直接看官网文档 :

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Specifier

Description

%a

Abbreviated weekday name (Sun..Sat)

%b

Abbreviated month name (Jan..Dec)

%c

Month, numeric (0..12)

%D

Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%f

Microseconds (000000..999999)

%H

Hour (00..23)

%h

Hour (01..12)

%I

Hour (01..12)

%i

Minutes, numeric (00..59)

%j

Day of year (001..366)

%k

Hour (0..23)

%l

Hour (1..12)

%M

Month name (January..December)

%m

Month, numeric (00..12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00..53), where Sunday is the first day of the week; WEEK() mode 0

%u

Week (00..53), where Monday is the first day of the week; WEEK() mode 1

%V

Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X

%v

Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x

%W

Weekday name (Sunday..Saturday)

%w

Day of the week (0=Sunday..6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

%%

A literal % character

%x

x, for any “x” not listed above

里面有1个%f,但是是6位的,如果毫秒只需要3位,再套一层substring,效果如下:

 上图也顺便给了另1个小技巧:默认情况下now()和current_timestamp()函数,只精确到秒,如果需要到毫秒,传入3或6这样的精度值即可。

0 人点赞