将时间戳转换为雪花
我在雪花中工作，并具有像这样的时间戳格式的列：
timestamp
2021-12-13T21:52:58.656216349+0000
2021-12-13T18:22:01.194783523+0000
2021-12-13T21:03:55.224874997+0000
2021-12-13T21:02:37.075422427+0000
2021-12-13T15:54:26.268433672+0000
我所需的输出的格式是：
2021-12-13
从过去的问题中搜索，我发现
SELECT
timestamp AS original_ts,
to_timestamp(REPLACE(REPLACE(timestamp,'T',' '),'+',''),'YYYY-MM-DD') AS modified_ts
FROM
table
parse'2022-01-26 00：06：11.1851022090000'作为时间戳，格式为'yyyy-mm-dd'
我如何解决此错误并将时间戳列格式化为更熟悉的日期列，以完全忽略时间？
I am working within Snowflake and have a column with a timestamp format like this:
timestamp
2021-12-13T21:52:58.656216349+0000
2021-12-13T18:22:01.194783523+0000
2021-12-13T21:03:55.224874997+0000
2021-12-13T21:02:37.075422427+0000
2021-12-13T15:54:26.268433672+0000
my desired output is in a format with:
2021-12-13
Searching from past questions, I found this answer and attempted to modify it for my use by:
SELECT
timestamp AS original_ts,
to_timestamp(REPLACE(REPLACE(timestamp,'T',' '),'+',''),'YYYY-MM-DD') AS modified_ts
FROM
table
but get the following error:
Can't parse '2022-01-26 00:06:11.1851022090000' as timestamp with format 'YYYY-MM-DD'
How can I resolve this error and format the timestamp column into a more familiar date column ignoring the time entirely?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我想您想使用 a>在这里与
左：请注意，如果您不需要真正的日期，而只是一个日期字符串，则
左（时间戳，10）仅就足够了。I think you want to use
TO_DATEhere along withLEFT:Note that if you don't require a bona fide date, but rather just a date string, then
LEFT(timestamp, 10)alone should suffice.如果使用date_trunc函数，则无需担心源列的格式，因此您不需要左功能 - 假设它是日期或时间戳列
If you use the DATE_TRUNC function then you don’t need to worry about the format of the source column, and therefore you don’t need the LEFT function - assuming it is a date or timestamp column