如何在标准SQL中将纪元时间戳转换为日期 [英] How to convert an Epoch timestamp to a Date in Standard SQL

问题描述

环顾四周时，我没有找到任何简单的答案，所以我想把它放在这里，以防有人遇到与我相同的问题，而这本来应该是个小问题.

I didn't find any simple answer to this while I was looking around, so I thought I'd put it up here in case anyone was having the same problem as me with what should have been a trivial issue.

我当时在Google的BigQuery中使用ReDash分析，并已打开

I was using ReDash analytics with Google's BigQuery and had turned on Standard SQL in the datasource settings. For the purposes of my query, I needed to convert a timestamp - unix time in milliseconds, as a string - to a Date format so that I could use the DATE_DIFF method.

作为示例... "1494865480000"至"2017-05-15"

困难在于转换和转换过于严格，而且似乎没有足够的方法对其进行解析.请在下面查看我的答案！ (尽管让我知道某些SQL老师是否知道一种更雄辩的方法！)

The difficulty was that casting and conversion was excessively strict and there seemed no adequate way to make it parse. See my answer down below! (Though let me know if some SQL sensei knows a more eloquent way!)

推荐答案

在标准SQL中，将TIMESTAMP_MICROS函数与EXTRACT(DATE FROM <timestamp>)一起使用:

In Standard SQL use TIMESTAMP_MICROS function together with EXTRACT(DATE FROM <timestamp>):

SELECT EXTRACT(DATE FROM TIMESTAMP_MILLIS(1494865480000))

这篇关于如何在标准SQL中将纪元时间戳转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！