blocks|key|2858345|text|您可以使用来实现这一点(单击链接查看更多其他格式)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2858346|SELECT+DATE_FORMAT(colName,+'%25Y-%25m-%25d')+DATEONLY,+
+++++++DATE_FORMAT(colName,'%25H:%25i:%25s')+TIMEONLY|code-block|syntax|javascript|2858347|SQLFiddle+Demo|offset|length|2858348|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/d41d8/1842^0|0|0|0|E|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

You can achieve that using <a href="http://davidwalsh.name/format-date-mysql-date_format" rel="noreferrer">DATE_FORMAT()</a> (click the link for more other formats)

<pre><code>SELECT DATE_FORMAT(colName, '%Y-%m-%d') DATEONLY, 
 DATE_FORMAT(colName,'%H:%i:%s') TIMEONLY
</code></pre>

<h1><a href="http://sqlfiddle.com/#!2/d41d8/1842" rel="noreferrer">SQLFiddle Demo</a></h1>

blocks|key|2858350|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2858351|SELECT+DATE(`date_time_field`)+AS+date_part,+TIME(`date_time_field`)+AS+time_part+FROM+`your_table`|code-block|syntax|javascript|2858352|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try:

<pre><code>SELECT DATE(`date_time_field`) AS date_part, TIME(`date_time_field`) AS time_part FROM `your_table`
</code></pre>

blocks|key|3071779|text|根据mysql文档，DATE()函数将提取日期时间字段的日期部分，而TIME()将提取时间部分。所以我会尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3071780|select+DATE(dateTimeField)+as+Date,+TIME(dateTimeField)+as+Time,+col2,+col3,+FROM+Table1+...|code-block|syntax|javascript|3071781|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

per the mysql documentation, the DATE() function will pull the date part of a datetime feild, and TIME() for the time portion. so I would try:
<pre><code>select DATE(dateTimeField) as Date, TIME(dateTimeField) as Time, col2, col3, FROM Table1 ...
</code></pre>

blocks|key|2858402|text|仅限日期使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2858403|date("Y-m-d");|offset|length|style|CODE|2858404|而且只在时间上使用|2858405|date("H:i:s");|2858406|entityMap^0|0|0|E|0|0|0|E|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

For only date use 
<code>date("Y-m-d");</code>

and for only time use 
<code>date("H:i:s");</code>

blocks|key|3071814|text|简单地说，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3071815|选择时间(Column_name)、日期(Column_name)|3071816|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Simply, 
SELECT TIME(column_name), DATE(column_name)

blocks|key|3071822|text|一种简单的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3071823|对于日期：|3071824|SELECT+DATE(column_name)+as+mydate|code-block|syntax|javascript|3071825|对于《时代》：|3071826|SELECT+DATE(column_name)+as+mytime|3071827|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

An easy way would be:
For date:
<pre><code>SELECT DATE(column_name) as mydate
</code></pre>
For time:
<pre><code>SELECT DATE(column_name) as mytime
</code></pre>

I am storing a DATETIME field in a Table. Each value looks something like this:
2012-09-09 06:57:12 .
I am using this syntax:
<pre><code> date(&quot;Y-m-d H:i:s&quot;);
</code></pre>
Now my question is, while fetching the data, how can get both date and time separately, using the single MySQL query?
Date like 2012-09-09 and time like 06:57:12.

How to part DATE and TIME from DATETIME in MySQL

MySQL

我将一个DATETIME字段存储在一个表中。每个值看起来像这样：2012-09-09 06:57:12。我使用的是以下语法：   date("Y-m-d H:i:s");现在我的问题是，在获取数据时，如何使用单个MySQL查询分别获取日期和时间？日期如2012-09-09，时间如06:57:12。

问如何在MySQL中将日期和时间与DATETIME分开
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中将日期和时间与DATETIME分开EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中将日期和时间与DATETIME分开
EN