Presto SQL/Athena:选择不同日期之间的时间 [英] Presto SQL / Athena: select between times across different days

问题描述

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

我发现自己需要选择整天在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.

这篇关于Presto SQL/Athena:选择不同日期之间的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！