MySQL date and time and transform timestamp function

The formula for using EXECL to convert timestamps is:

Code:

= (xxxxxxxxxx+8*3600)/86400+70*365+19

Use the MySQL tutorial statement to explain timestamp syntax examples:

Code:

Select From_unixtime (1234567890, '%y-%m-%d%h:%i:%s ')

Report:

In MySQL, the storage type of a time field is int (11), how to convert to a character type, for example, storage is 13270655222, it needs to be converted into a yyyy-mm-dd form.

Use the From_unixtime function as follows:

Code:

From_unixtime (Unix_timestamp,format)

Returns a String representing the Unix time token, formatted according to the format string. Format can contain the same modifiers as the entries listed in the Date_format () function.

Formats the date value based on the format string. The following modifiers can be used in the format string:%m month name (january......december)

%w Week name (sunday......saturday)

%d the date of the month with English prefix (1st, 2nd, 3rd, and so on). )

%y years, numbers, 4-bit

%y years, numbers, 2-bit

%a Abbreviated week name (SUN......SAT)

Days in%d months, numbers (00 ...). 31)

%e the number of days in the month, numbers (0 ...). 31)

%m Month, number (01 ...) 12)

%c month, number (1 ...). 12)

%b Abbreviated month name (JAN......DEC)

%j the number of days in a year (001 ...). 366)

%h hours (00 .....) 23)

%k hours (0 .....) 23)

%h hours (01 .....) 12)

%i hours (01 .....) 12)

%l hours (1 .....) 12)

%i minutes, Number (00 .....) 59)

%r time, 12 hours (Hh:mm:ss [ap]m)

%t time, 24 hours (HH:MM:SS)

%s seconds (00 ...). 59)

%s seconds (00 ...). 59)

%p am or PM

%w the number of days in one weeks (0=sunday ...). 6=saturday)

%u weeks (0 .....) 52), here Sunday is the first day of the week

%u weeks (0 .....) 52), here Monday is the first day of the week

Percent% of a text "%".

Related functions

Mysql> Select something from Tbl_name

where To_days (now ()) –to_days (date_col) <= 30;

DayOfWeek (date)

Returns the week index of date (1 = Sunday, 2 = Monday, ...). 7 = Saturday). Index values are compliant with ODBC standards.

Mysql> Select DayOfWeek (' 1998-02-03′);

-> 3

Weekday (date)

Returns the week index of date (0 = Monday, 1 = Tuesday, ...). 6 = Sunday):

Mysql> Select Weekday (' 1998-02-03 22:23:00′);

-> 1

Mysql> Select Weekday (' 1997-11-05′);

-> 2

DayOfMonth (date)

Returns date is the first day of January, ranging from 1 to 31:

Mysql> Select DayOfMonth (' 1998-02-03′);

-> 3

DayOfYear (date)

Returns date is the first day of the year, ranging from 1 to 366:

Mysql> Select DayOfYear (' 1998-02-03′);

-> 34

Month (date)

Returns the month in date, ranging from 1 to 12:

Mysql> Select month (' 1998-02-03′);

-> 2

Dayname (date)

Returns the week name of date:

Mysql> Select Dayname ("1998-02-05′");

-> ' Thursday '

MonthName (date)

Returns the month name of date:

Mysql> Select MonthName ("1998-02-05′");

-> ' February '

Quarter (date)

Returns date in the quarter of the year, with a range of 1 to 4:

Mysql> Select quarter (' 98-04-01′);

-> 2

Week (date)

Week (Date,first)