当前位置：网站首页>How does MySQL convert stored seconds into dates

How does MySQL convert stored seconds into dates

2022-04-23 06:05:00 【fusheng-fate】

The type of date the database stores

There are many ways to store time in a database , For different considerations, storage methods are also different , Several common types are ：timestamp,int( Number of seconds ),bigint( Number of milliseconds ),date( Specific date ),time( when 、 branch 、 second ).
When the time stored in the database is seconds or milliseconds , How to convert it to a specific date , Can pass FROM_UNIXTIME(sec,format) Functions and UNIX_TIMESTAMP(date) The function implements seconds / Conversion of milliseconds and date .

FROM_UNIXTIME(sec,format)

The function converts seconds to dates
Parameters sec It refers to the number of seconds ,format Refers to the format

select FROM_UNIXTIME(u_registryDate,'%Y-%m-%d %h:%i:%s') from u_user

UNIX_TIMESTAMP(date)

Convert date to seconds
date It's the date

select UNIX_TIMESTAMP(date) from u_user

SEC_TO_TIME(sec)

Convert seconds to hours : minute : Second form

select SEC_TO_TIME(u_registryDate) from u_user

TIME_TO_SEC(date)

Convert time to seconds ,date Format X when X branch X second , If it is in the format of hour, minute and second of month, year and day, only turn the following hour, minute and second

select TIME_TO_SEC(date) from u_user

attach

Date common operation specifier ：

specifier	explain
%a	The abbreviated name of a weekday （Sun~Sat)
%b	Abbreviated name of the month （Jan…Dec)
%c	month , Digital form （0~12)
%D	Date of the month with English suffix (0th, 2st, 3nd,…）
%d	Date of the month , Digital form （00~31)
%e	Date of the month , Digital form （(0~31)
%f	Microsecond （000000 …999999)
%H	With 2 Digit representation 24 Hours （00~23)
%h, %I	With 2 Digit representation 12 Hours （01~12)
%i	minute , Digital form (00~59)
%j	— Days of the year （001~366)
%k	With 24 Hours （0~23) Express
%l	With 12 Hours （1~12) Express
%M	The name of the month （January~December)
%m	month , Digital form （00~12)
%p	In the morning （AM) Or afternoon （PM)
%r	Time ,12 hourly （ Hours (hh): minute (mm) : Number of seconds (ss) After add AM or PM)
%S, %s	With 2 The number of digits represents seconds （00~59)
%T	Time ,24 hourly （ Hours (hh): minute (mm): Number of seconds (ss))
%U	Zhou （00~53), Sunday is the first day of the week
%u	Zhou （00~53), Monday is the first day of the week
%V	Zhou （01~53), Sunday is the first day of the week , and ％X Use at the same time
%v	Zhou （01~53), Monday is the first day of the week , and %x Use at the same time
%W	Week logo （ Sunday 、 Monday 、 Tuesday … Saturday ）
%w	— Every day of the week （0= Sunday …6= Saturday ）
%X	The year of the week , Sunday is the first day of the week , Digital form ,4 digit , and ％V Use at the same time
%x	The year of the week , Monday is the first day of the week , Digital form ,4 digit , and %v Use at the same time
%Y	4 The number of digits represents the year
%y	2 The number of digits represents the year
%%	% A literal character