Mysql date and time and the conversion timestamp Function

Source: Internet
Author: User
Tags month name
The formula for using execl to convert the timestamp is: Code :( xxxxxxxxxx + 8*3600) 86400 + 70*365 + 19 use statements to explain the timestamp syntax example: Code: selectfrom_unixtime (1234567890, % y-% m-% d % h: % I: % s) attached: in mysql, the storage type of a time field is int (11 ), how to convert to the character type? For example, if the storage is 13270655222, the formula for using execl to convert the timestamp is:

Code:
= (Xxxxxxxxxx + 8*3600)/86400 + 70*365 + 19

Example of Time Stamp syntax interpretation using statements:

Code:

Select from_unixtime (1234567890, '% y-% m-% d % h: % I: % s ')

Appendix:
In mysql, the storage type of a time field is int (11). How can it be converted to the character type? For example, if the storage is 13270655222, it must be converted to the yyyy-mm-dd format.
Use the from_unixtime function as follows:

Code:
From_unixtime (unix_timestamp, format)
Returns a string representing the unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the date_format () function.
Format the date value based on the format string. The following modifier can be used in the format string: % m month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, 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 days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% 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 ). The index value complies with odbc standards.
Mysql> select dayofweek ('2017-02-03 ′);
-> 3

Weekday (date)
Returns the week index of date (0 = Monday, 1 = Tuesday ,... 6 = Sunday ):
Mysql> select weekday ('2017-02-03 22:23:00 ′);
-> 1
Mysql> select weekday ('2017-1997 ′);
-> 2

Dayofmonth (date)
The return date is the day of January 1, January, ranging from 1 to 31:
Mysql> select dayofmonth ('2017-02-03 ′);
-> 3

Dayofyear (date)
Returns the day of the year from 1 to 366:
Mysql> select dayofyear ('2017-02-03 ′);
-> 34

Month (date)
Returns the month from date, ranging from 1 to 12:
Mysql> select month ('2017-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 the quarter of a year in which the date value ranges from 1 to 4:
Mysql> select quarter ('98-04-01 ′);
-> 2

Week (date)
Week (date, first)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.