blocks|key|515645|text|最简单的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|515646|SELECT+id,name,FROM_UNIXTIME(registration_date)+FROM+`tbl_registration`;|code-block|syntax|javascript|515647|这至少为日期列提供了一种可读的格式。此外，如果您想更改te格式，请单击here。|offset|length|515648|entityMap|0|LINK|mutability|MUTABLE|url|https://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php^0|0|0|Z|4|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]]]]

The simplest way of doing this is: 

<pre><code>SELECT id,name,FROM_UNIXTIME(registration_date) FROM `tbl_registration`;
</code></pre>

This gives the date column atleast in a readable format.
Further if you want to change te format click <a href="https://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php" rel="noreferrer">here</a>.

blocks|key|731938|text|我的同事帮助我做到了这一点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|731939|select+CONVERT(VARCHAR(10),+<tms_column>,+112),+count(*)
from+table+where+<tms_column>+>+'2012-09-10'
group+by+CONVERT(VARCHAR(10),+<tms_column>,+112);|code-block|syntax|javascript|731940|或|731941|select+CONVERT(DATE,+<tms_column>,+112),+count(*)
from+table+where+<tms_column>+>+'2012-09-10'
group+by+CONVERT(DATE,+<tms_column>,+112);|731942|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

My coworkers helped me with this:

<pre><code>select CONVERT(VARCHAR(10), &lt;tms_column&gt;, 112), count(*)
from table where &lt;tms_column&gt; &gt; '2012-09-10'
group by CONVERT(VARCHAR(10), &lt;tms_column&gt;, 112);
</code></pre>

or

<pre><code>select CONVERT(DATE, &lt;tms_column&gt;, 112), count(*)
from table where &lt;tms_column&gt; &gt; '2012-09-10'
group by CONVERT(DATE, &lt;tms_column&gt;, 112);
</code></pre>

blocks|key|515524|text|使用cast，您可以从时间戳字段中获取日期：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|515525|SELECT+CAST(timestamp_field+AS+DATE)+FROM+tbl_name|code-block|syntax|javascript|515526|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|$]]

Using cast you can get date from a timestamp field:

<pre><code>SELECT CAST(timestamp_field AS DATE) FROM tbl_name
</code></pre>

blocks|key|732171|text|“你一直在用这个词。我认为它的意思和你想的不一样。”--伊尼戈·蒙托亚|type|unstyled|depth|inlineStyleRanges|entityRanges|data|732172|正如marc_s最初所说的，时间戳与时间完全没有关系。|732173|declare+@Test+table+(
+++++TestId+int+identity(1,1)+primary+key+clustered
++++,Ts+++++timestamp
++++,CurrentDt+datetime+default+getdate()
++++,Something+varchar(max)
)

insert+into+@Test+(Something)
++++select+name+from+sys.tables
waitfor+delay+'00:00:10'

insert+into+@Test+(Something)
++++select+name+from+sys.tables

select+*+from+@Test|code-block|syntax|javascript|732174|请注意，在输出中，每条记录的Ts+(十六进制)都会递增1，但实际时间间隔为10秒。如果它与时间相关，那么在时间戳中就会有一个间隙，以对应于时间上的差异。|732175|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

"You keep using that word. I do not think it means what you think it means."
— Inigo Montoya

The timestamp has absolutely no relationship to time as marc_s originally said. 

<pre><code>declare @Test table (
 TestId int identity(1,1) primary key clustered
 ,Ts timestamp
 ,CurrentDt datetime default getdate()
 ,Something varchar(max)
)

insert into @Test (Something)
 select name from sys.tables
waitfor delay '00:00:10'

insert into @Test (Something)
 select name from sys.tables

select * from @Test
</code></pre>

Notice in the output that Ts (hex) increments by one for each record, but the actual time has a gap of 10 seconds. If it were related to time then there would be a gap in the timestamp to correspond with the difference in the time.

blocks|key|515813|text|罗伯特·毛罗的评论是正确的。对于那些知道Sybase起源的人来说，datetime实际上是两个单独的整数，一个代表日期，一个代表时间，所以timestamp+aka+rowversion可以被认为是从服务器捕获的原始值。快得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|515814|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Robert Mauro has the correct comment. For those who know the Sybase origins, datetime was really two separate integers, one for date, one for time, so timestamp aka rowversion could just be considered the raw value captured from the server. Much faster.

blocks|key|732211|text|对于我的作品:+TO_DATE('19700101'，'yyyymmdd')+%2B+(TIME+/+24+/+60+/+60)+(oracle+DB)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|732212|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

for me works:
TO_DATE('19700101', 'yyyymmdd') + (TIME / 24 / 60 / 60)
(oracle DB)

blocks|key|732251|text|我对时间戳也有同样的问题，例如：‘29-JUL-20+04.46.42.000000000+PM’。我想把它变成'yyyy-MM-dd‘格式。最终对我有效的解决方案是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|732252|从mytable中选择TO_CHAR(mytimestamp，'YYYY-MM-DD')；|732253|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|$]]

I had the same problem with timestamp eg:'29-JUL-20 04.46.42.000000000 PM'. I wanted to turn it into 'yyyy-MM-dd' format. The solution that finally works for me is
SELECT TO_CHAR(mytimestamp, 'YYYY-MM-DD') FROM mytable;

blocks|key|515490|text|为什么不试试FROM_UNIXTIME(unix_timestamp,+format)呢？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|515491|entityMap^0|6|11|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Why not try <code>FROM_UNIXTIME(unix_timestamp, format)</code>?

blocks|key|515585|text|我不确定我是否遗漏了什么，但你不能像这样转换时间戳吗：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|515586|CONVERT(VARCHAR,CAST(ZEIT+AS+DATETIME),+110)|code-block|syntax|javascript|515587|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|$]]

Not sure if I'm missing something here but can't you just convert the timestamp like this:

<pre><code>CONVERT(VARCHAR,CAST(ZEIT AS DATETIME), 110)
</code></pre>

As SQL Server returns timestamp like <code>'Nov 14 2011 03:12:12:947PM'</code>, is there some easy way to convert string to date format like 'Y-m-d H:i:s'.

So far I use 

<pre><code>date('Y-m-d H:i:s',strtotime('Nov 14 2011 03:12:12:947PM'))
</code></pre>

How to convert SQL Server's timestamp column to datetime format

SQL Server

服务器

当SQL Server像'Nov 14 2011 03:12:12:947PM'一样返回时间戳时，有没有什么简单的方法可以把字符串转换成像'Y-m-d H:i:s‘这样的日期格式呢？到目前为止我使用的是date('Y-m-d H:i:s',strtotime('Nov 14 2011 03:12:12:947PM'))

问如何将SQL Server的时间戳列转换为日期时间格式
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将SQL Server的时间戳列转换为日期时间格式EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将SQL Server的时间戳列转换为日期时间格式
EN