英文:
Date_trunc error to truck the user activity on website
问题
我正在使用谷歌云，尝试格式化event_timestamp列以提取日期和时间，以识别用户在网站上进行购买所需的时间。
DATE_TRUNC(DATE(event_timestamp), 'month') AS purchase_date,
根据查询，我收到了错误信息“在[6:31]处需要有效的日期部分名称”
数据集
| event_timestamp |
| ------------------ |
| 1605430896492843 |
期望的结果示例
| Purchase_date |
| ----------------------|
| 2020-11-15 12:27:20 |
英文:
I'm using google cloud and trying to format the event_timestamp column to extract the date and time to identifying time that user take to make a purchase on website
DATE_TRUNC(DATE (event_timestamp), 'month') AS purchase_date,
as per the query, I got an error "A valid date part name is required at [6:31]"
the dateset
| event_timestamp |
|---|
| 1605430896492843 |
expecting results example
| Purchase_date |
|---|
| 2020-11-15 12:27:20 |
答案1
得分: 1
由于你的 event_timestamp 值包含 16 位数字，你需要使用 timestamp_micros 函数。
> timestamp_micros 将 int64_expression 解释为自 1970-01-01 00:00:00 UTC 以来的微秒数，并返回时间戳。
select timestamp_micros(event_timestamp) AS purchase_date
这将输出 (2020-11-15 09:01:36.492843 UTC) ，对应输入值 (1605430896492843)。
date_trunc /timestamp_trunc 函数将日期截断到给定的 date_part，所以根据你的预期输出，你不需要使用它。
select timestamp_trunc(timestamp_micros(1605430896492843), month) AS purchase_date
这将输出 (2020-11-01 00:00:00 UTC)
英文:
Since your event_timestamp value consists of 16 digits, you need to use the timestamp_micros function.
> timestamp_micros Interprets int64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC and returns a timestamp.
select timestamp_micros(event_timestamp) AS purchase_date
This will output (2020-11-15 09:01:36.492843 UTC) for the input (1605430896492843).
The date_trunc /timestamp_trunc function will truncate the date to the given date_part, so according to your expected output you don't need to use it.
select timestamp_trunc(timestamp_micros(1605430896492843), month) AS purchase_date
This will output (2020-11-01 00:00:00 UTC)
答案2
得分: 0
根据文档，似乎应该去掉引号：
DATE_TRUNC(DATE(event_timestamp), MONTH) AS purchase_date
英文:
From the docs it seems, that it should be without quotes:
DATE_TRUNC(DATE (event_timestamp), MONTH) AS purchase_date
答案3
得分: 0
更改顺序：
DATE_TRUNC('month', DATE (event_timestamp)) 作为 purchase_date
英文:
Change the order:
DATE_TRUNC('month', DATE (event_timestamp)) AS purchase_date
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论