创建日期范围表 [英] Creating a date range table

问题描述

我正在编写一份需要每天显示一个值的报告.我有查询的开始和结束日期，但我希望避免在表不包含特定日期值的情况下丢失天数.我正在考虑创建一个包含开始和结束之间所有天数的基准日期范围表，然后将它与数据表连接起来以显示每一天的值.

I'm writing a report that needs to display a value per day. I have the start and end date for the query, but I wish to avoid missing days in case the table does not contain a value for a specific date. I was thinking about creating a base date range table that holds all days between start and end, then left join it with the data table to show a value for each day.

我找到了一些用于 mySQL、SQL Server 等的脚本，但没有用于 SQLite.有没有办法用日期范围快速填充表格?

I found a few scripts for mySQL, SQL Server, etc.. but none for SQLite. Is there a way to quickly populate a table with a date range?

谢谢

推荐答案

以下是使用数字表扩展范围的方法:

Here's how you could use a numbers table to expand a range:

SELECT
  datetime('now', N || ' seconds') AS DT
FROM numbers
WHERE N < strftime('%s', 'now', '1 minutes') - strftime('%s', 'now');

在这种情况下，数字表应该保存从 0 开始的数字.

In this case, the numbers table is supposed to hold numbers starting from 0.

数字表是一种值得随身携带的工具，可用于多种用途.您可以像这样初始化它，例如:

A numbers table is a tool worth keeping handy for many purposes. You could initialise it like this, for instance:

CREATE TABLE numbers (N int);
/* #0 */ INSERT INTO numbers (N) SELECT 0;
/* #1 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #2 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #3 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #4 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #5 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #6 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #… */

每行 #N 导致表中的 2^N 行，最大的数是 2N-1

可以在 SQL Fiddle 上找到(并使用)该方法的演示.

这篇关于创建日期范围表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！