Presto SQL/Athena:选择不同日期之间的时间

本文介绍了Presto SQL/Athena:选择不同日期之间的时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含一系列事件及其时间戳的数据库.

我发现自己需要选择整天在11:00和11:10以及21:00和21:05之间发生的所有事件.

所以我要做的是从时间戳中提取小时和分钟，并且:

SELECT *在哪里(小时= 11 AND分钟<= 10)或(小时= 21 AND分钟< = 05)

但是，我想知道是否有一种更简单/更少冗长的方法来做到这一点，例如在日期之间查询时:

SELECT *"2020-07-01"和"2020-07-05"之间的日期

我在此处读到在SQLite中是可行的，我想知道是否也可以预先完成.我看过文档，但是找不到在SQLite中执行 time()的模拟功能.

解决方案

您可以使用日期格式设置功能，例如 date_format ，然后进行字符串比较:

选择*来自mytable在哪里date_format(mydate，'％H:％i')在'11:00'和'11:09'之间或介于'21:00'和'21:04'之间的date_format(mydate，'％H:％i')

请注意，由于假设您不想包括最后一分钟，因此我从上限减去了一分钟.介于"11:00"和"11:09"之间为您提供从 11:00:00 到 11:09:59 的所有内容./p>

I have a database that contains a series of events and their timestamp.

I find myself needing to select all events that happen between 11:00 and 11:10 and 21:00 and 21:05, for all days.

So what I would do is I extract from timestamp the hour and the minute, and:

SELECT * WHERE (hour = 11 AND minute <= 10) OR (hour = 21 AND minute <= 05)

However, I was wondering if there's a simpler / less verbose way to do this, such as when you query between dates:

SELECT * WHERE date BETWEEN '2020-07-01' AND '2020-07-05'

I read here that this is doable in SQLite, I was wondering if it's possible to be done in presto as well. I've looked at the docs but couldn't find an analogue function that does what time() does in SQLite.

解决方案

You could use date formatting functions, e.g. date_format, then string comparisons:

select * from mytable where date_format(mydate, '%H:%i') between '11:00' and '11:09' or date_format(mydate, '%H:%i') between '21:00' and '21:04'

Note that I substracted one minute from the upper bound, since I assume you don't want to include the last minute. between '11:00' and '11:09' gives you everything from 11:00:00 to 11:09:59.