MySQL Date and Unix timestamp are converted to each other

Source: Internet
Author: User
Tags mysql query

Sample code:






1

2


#先把当前时间转化成UNIX时间戳,然后在格式化成日期

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()),  '%Y-%m-%d|%H:%i:%s|%W|%w|%j|%T|%p|%r|%U|%u|%%' AS '把UNIX时间戳转化成日期' ;

Second, convert the date into a Unix timestamp.

Function: Unix_timestamp.

Syntax: Unix_timestamp (date);//data is not a required parameter, the default is the current time, a UNIX timestamp (unsigned integer) that returns a date tag.

Sample code:






1

2


#不加 date 参数

SELECT UNIX_TIMESTAMP()  AS '当前时间UNIX时间戳' ;






1

2


#加 date 参数

SELECT UNIX_TIMESTAMP( '2014-04-17 10:33:35' AS '把日期转化成UNIX时间戳' ;

Unix timestamp converted to date with function From_unixtime ()

Select From_unixtime (1156219870);

Date converted to Unix timestamp with function unix_timestamp ()

Select unix_timestamp (' 2006-11-04 12:23:00′);

Example: MySQL Query the number of records of the day:

$sql = "SELECT * FROM Message Where date_format (From_unixtime (chattime), '%y-%m-%d ') = Date_format (now (), '%y-%m-%d ') ORDER BY id DESC ";

Let's look at some examples.

S


First, turn the Unix timestamp into a date.

Function: From_unixtime.

Syntax: From_unixtime (unix_timestamp, format);//Returns a date string (is a string type, not a date type) for the Unix timestamp token.

The format modifiers are as follows:

The year of the

1, year
%y:4 bit (2014).
The year of the%y:2 bit (14).
2, Month
%m: English full spelling (January ...) December).
%b: English abbreviation (...) DEC).
%m: Number (01 ...). 12).
%c: Number (1 ...). 12).
3, Day
%d: with English suffix (1st, 2nd, 3rd ...).
%d: Number (00 ...). 31).
%e: Number (0 ...). 31).
4, when
%H: Number (00 ...). 23).
%k: Number (0 ...). 23).
%h: Number (01 ...). 12).
%I: Number (01 ...). 12).
5, Minutes
%i: Number (00 ...). 59).
6, Sec
%s: Number (00 ...). 59).
%s: Number (00 ...). 59).
7, Week
%w: English full spelling (Sunday ...) Saturday).
%a: English abbreviation (Sun ...) Sat).
%w: Number (0=sunday ...) 6=saturday).
8, day of the year
%j: Number (001 ...). 366).
9, other
%p:am or PM
%T: Time, 24 hours (HH:MM:SS).
%r: Time, 12 hours (Hh:mm:ss [ap]m).
%u: The first few weeks of the year (0 ...). 52), here Sunday is the first day of the week.
%u: The first few weeks of the year (0 ...). 52), here Monday is the first day of the week.
percent%: a literal "%".

Related Article

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.