草庐IT

mysql - 选择时间戳最接近的行

coder 2023-10-19 原文

我有一个类似于下表的表格 - 主要包含时间戳以及其他一些列:

WeatherTable
+---------------------+---------+----------------+      +
| TS                  | MonthET | InsideHumidity | .... |
+---------------------+---------+----------------+      |
| 2014-10-27 14:24:22 |       0 |             54 |      |
| 2014-10-27 14:24:24 |       0 |             54 |      |
| 2014-10-27 14:24:26 |       0 |             52 |      |
| 2014-10-27 14:24:28 |       0 |             54 |      |
| 2014-10-27 14:24:30 |       0 |             53 |      |
| 2014-10-27 14:24:32 |       0 |             55 |      |
| 2014-10-27 14:24:34 |       9 |             54 |      |
.......

我正在尝试制定一个 SQL 查询，该查询以特定的任意粒度(例如，每 15 秒)返回特定时间范围内的所有行(此处没有问题)。该数字始终以秒为单位指定，但不限于小于 60 的值。更复杂的是，时间戳不一定落在所需的粒度上，因此不能简单地选择 14:24:00 的时间戳, 14:24:15, 14:24:30 等 - 时间戳与每个值最接近的行需要包含在结果中。

例如，如果开始时间为 14:24:30，结束时间为 14:32:00，粒度为 130，则理想时间为:

14:24:30
14:26:40
14:28:50
14:31:00

但是，时间戳可能不存在于每个时间，在这种情况下，应选择时间戳与每个理想时间戳最接近的行。如果两个时间戳距离理想时间戳的距离相等，则应选择较早的时间戳。

数据库是 Web 服务的一部分，所以目前我只是忽略了 SQL 查询中的粒度，稍后在 (Java) 代码中过滤掉不需要的结果。但是，就内存消耗和性能而言，这似乎远非理想。

有什么想法吗？

最佳答案

你可以尝试这样做:

首先创建一个时间间隔列表。使用 Get a list of dates between two dates 中的存储过程 make_intervals创建一个以某种方式调用它的临时表:

call make_intervals(@startdate,@enddate,15,'SECOND');

然后您将有一个表time_intervals，其中两列之一名为interval_start。使用它来找到最接近每个间隔的时间戳，就像这样:

CREATE TEMPORARY TABLE IF NOT EXISTS time_intervals_copy
  AS (SELECT * FROM time_intervals);

SELECT
  time_intervals.interval_start,
  WeatherTable.*
FROM time_intervals
JOIN WeatherTable
  ON WeatherTable.TS BETWEEN @startdate AND @enddate
JOIN (SELECT
        time_intervals.interval_start AS interval_start,
        MIN(ABS(time_intervals.interval_start - WeatherTable.TS)) AS ts_diff
      FROM time_intervals_copy AS time_intervals
      JOIN WeatherTable
      WHERE WeatherTable.TS BETWEEN @startdate AND @enddate
      GROUP BY time_intervals.interval_start) AS min
  ON min.interval_start = time_intervals.interval_start AND
     ABS(time_intervals.interval_start - WeatherTable.TS) = min.ts_diff
GROUP BY time_intervals.interval_start;

这将找到与每个 time_interval 最接近的时间戳。注意:WeatherTable 中的每一行都可以列出多次，如果使用的间隔小于存储数据间隔的一半(或类似的东西，你明白了；)).

注意:我没有测试查询，它们是我脑子里写的。请根据您的用例进行调整并更正可能存在的小错误...

关于mysql - 选择时间戳最接近的行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/26705544/

mysql选择intervalstime_intervalsinterval_startsql

有关mysql - 选择时间戳最接近的行的更多相关文章

  1. ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2

    我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby​​是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。 最佳答案 我不太明白你的问题。我假设您想检查

  2. ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2

    这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build

  3. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

  4. ruby - Rails 3 的 RGB 颜色选择器 - 2

    状态:我正在构建一个应用程序，其中需要一个可供用户选择颜色的字段，该字段将包含RGB颜色代码字符串。我已经测试了一个看起来很漂亮但效果不佳的。它是“挑剔的颜色”，并托管在此存储库中:https://github.com/Astorsoft/picky-color.在这里我打开一个关于它的一些问题的问题。问题:请建议我在Rails3应用程序中使用一些颜色选择器。 最佳答案 也许页面上的列表jQueryUIDevelopment:ColorPicker为您提供开箱即用的产品。原因是jQuery现在包含在Rails3应用程序中，因此使用基

  5. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  6. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  7. ruby - 我正在学习编程并选择了 Ruby。我应该升级到 Ruby 1.9 吗？ - 2

    我完全不是程序员，正在学习使用Ruby和Rails框架进行编程。我目前正在使用Ruby1.8.7和Rails3.0.3，但我想知道我是否应该升级到Ruby1.9，因为我真的没有任何升级的“遗留”成本。缺点是什么？我是否会遇到与普通gem的兼容性问题，或者甚至其他我不太了解甚至无法预料的问题？ 最佳答案 你应该升级。不要坚持从1.8.7开始。如果您发现不支持1.9.2的gem，请避免使用它们(因为它们很可能不被维护)。如果您对gem是否兼容1.9.2有任何疑问，您可以在以下位置查看:http://www.railsplugins.or

  8. ruby - 在没有基准或时间的情况下用 Ruby 测量用户时间或系统时间 - 2

    因为我现在正在做一些时间测量，我想知道是否可以在不使用Benchmark类或命令行实用程序time的情况下测量用户时间或系统时间。使用Time类只显示挂钟时间，而不显示系统和用户时间，但是我正在寻找具有相同灵active的解决方案，例如time=TimeUtility.now#somecodeuser,system,real=TimeUtility.now-time原因是我有点不喜欢Benchmark，因为它不能只返回数字(编辑:我错了-它可以。请参阅下面的答案。)。当然，我可以解析输出，但感觉不对。*NIX系统的time实用程序也应该可以解决我的问题，但我想知道是否已经在Ruby中实

  9. ruby-on-rails - Rails 单选按钮 - 模型中多列的一种选择 - 2

    我希望用户从一个模型的三个选项中选择一个。即我有一个模型视频，可以被评为正面/负面/未知目前我有三列bool值(pos/neg/unknown)。这是处理这种情况的最佳方式吗？为此，表单应该是什么样的？目前我有类似的东西但显然它允许多项选择，而我试图将它限制为只有一个..怎么办？ 最佳答案 如果要使用字符串列，让我们说rating。然后在你的表单中:#...#...它只允许一个选择编辑完全相同但使用radio_button_tag: 关于ruby-on-rails-Rails单选按钮-模

  10. ruby-on-rails - CarrierWave - PDF - 只选择第一页 - 2

    我的Rails应用程序中安装了carrierwave。但是，当用户上传多页pdf时，我只希望应用程序获取文档中的第一页并将其转换为jpeg。这可能吗？用什么命令？这是我的uploader。#encoding:utf-8classImageUploader[200,300]##defscale(width,height)##dosomething#end#Createdifferentversionsofyouruploadedfiles:version:thumbdoprocess:resize_to_fill=>[150,210]process:convert=>:jpgdefful

随机推荐