logo标签列表

从日期范围生成天数

sqlmysqldatetimebetween
160

我想要运行一个查询，例如

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

并返回像这样的数据：

天数
----------
2010年01月20日
2010年01月21日
2010年01月22日
2010年01月23日
2010年01月24日
11
这个问题没有其他附属问题。上述问题是关于掌握SQL课程的问题。 - Pentium10
你只需要根据所选日期范围获取一个日期数组吗？ - Derek Adair
1
我在思考一个用法，为了找到你的问题... 如果你得到一个任务，在你的表中填写一些缺失的记录。而且你必须为每一天运行一个查询，我在想类似这样的东西 insert into table select ... as days date between '' and '' - Pentium10
16
一个使用示例是生成统计数据，并包括没有数据的日期行。如果要进行分组，实际上在SQL中生成所有信息并将其添加到所需的任何格式中会比将数据原样转储到语言中，并开始循环和添加空值更快。 - Nanne
1
@Nanne 这正是我保存这个问题的原因。我需要将上述内容 LEFT JOIN 到可能不存在某些日期数据中。 - Josh Diehl
涵盖了“获取两个日期之间的日期列表”（尽管这个问题的焦点更窄，具体而言，另一个问题是与关系型数据库无关的）。 - outis
30个回答
0

SQLite 版本的 RedFilters 最佳解决方案

select d.Date
from (
    select 
    date(julianday('2010-01-20') + (a.a + (10 * b.a) + (100 * c.a))) as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) d
where 
d.Date between '2010-01-20' and '2010-01-24' 
order by d.Date
0

如果你想得到两个日期之间的日期列表：

create table #dates ([date] smalldatetime)
while @since < @to
begin
     insert into #dates(dateadd(day,1,@since))
     set @since = dateadd(day,1,@since)
end
select [date] from #dates

在这里玩弄代码：http://sqlfiddle.com/#!6/9eecb/3469

0

通过加入自定义假期表，改进了星期几和Microsoft MSSQL 2012的PowerPivot日期表。 https://gist.github.com/josy1024/cb1487d66d9e0ccbd420bc4a23b6e90e

with [dates] as (
    select convert(datetime, '2016-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '2018-01-01' --end
)
select [date]
, DATEPART (dw,[date]) as Wochentag
, (select holidayname from holidaytable 
where holidaytable.hdate = [date]) 
as Feiertag
from [dates]
where [date] between '2016-01-01' and '2016-31-12'
option (maxrecursion 0)
0
DELIMITER $$
CREATE PROCEDURE GenerateRangeDates(IN dateStart DATE, IN dateEnd DATE)
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE);

    loopDate: LOOP
        INSERT INTO dates(day) VALUES (dateStart); 
        SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);

        IF dateStart <= dateEnd 
            THEN ITERATE loopDate;
            ELSE LEAVE loopDate;
        END IF;
    END LOOP loopDate;

    SELECT day FROM dates;
    DROP TEMPORARY TABLE IF EXISTS dates;

END 
$$

-- Call procedure
call GenerateRangeDates( 
        now() - INTERVAL 40 DAY,
        now()
    );
0

类似于D'Arcy Rittich的回答，但是针对SQL SERVER

;WITH t AS
(
    SELECT n = a.n * 10 + b.n * 100 + c.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)
)
SELECT DATEADD(DAY, t.n, '2022-01-01')
FROM t
ORDER BY T.n;

或者没有CTE

SELECT DATEADD(DAY, nums.n, '2022-01-01') AS d FROM (
    SELECT n = a.n * 10 + b.n * 100 + c.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)
) nums
order by d
0
可以创建一个过程，用不同于日期的时间戳创建日历表。 如果您想为每个季度创建一个表格。
例如。
2019-01-22 08:45:00
2019-01-22 09:00:00
2019-01-22 09:15:00
2019-01-22 09:30:00
2019-01-22 09:45:00
2019-01-22 10:00:00

你可以使用

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_calendar_table`()
BEGIN

select unix_timestamp('2014-01-01 00:00:00') into @startts;
select unix_timestamp('2025-01-01 00:00:00') into @endts;

if ( @startts < @endts ) then

    DROP TEMPORARY TABLE IF EXISTS calendar_table_tmp;

    CREATE TEMPORARY TABLE calendar_table_tmp (ts int, dt datetime); 

    WHILE ( @startts < @endts)
        DO 
        SET @startts = @startts + 900;
        INSERT calendar_table_tmp VALUES (@startts, from_unixtime(@startts));
    END WHILE;

END if;

END

然后通过操作进行处理

select ts, dt from calendar_table_tmp;

这也给你ts

'1548143100', '2019-01-22 08:45:00'
'1548144000', '2019-01-22 09:00:00'
'1548144900', '2019-01-22 09:15:00'
'1548145800', '2019-01-22 09:30:00'
'1548146700', '2019-01-22 09:45:00'
'1548147600', '2019-01-22 10:00:00'

从这里开始，您可以添加其他信息，例如

select ts, dt, weekday(dt) as wd from calendar_table_tmp;

或者使用创建表语句来创建一个真正的表格

0

对于Oracle，我的解决方案是：

select trunc(sysdate-dayincrement, 'DD') 
  from dual, (select level as dayincrement 
                from dual connect by level <= 30)

Sysdate可以更改为特定日期，级别数字可以更改以提供更多日期。

0
set language  'SPANISH'
DECLARE @table table(fechaDesde datetime , fechaHasta datetime ) 
INSERT @table VALUES('20151231' , '20161231');
WITH x AS 
    (
        SELECT   DATEADD( m , 1 ,fechaDesde ) as fecha  FROM @table
        UNION ALL
        SELECT  DATEADD( m , 1 ,fecha )
        FROM @table t INNER JOIN x ON  DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
    )
SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id 
,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
,DATEPART ( mm , fecha ) Mes_Id
,DATEPART ( yy , fecha ) Anio
,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
,datename(MONTH, fecha) mes
,'Q' + convert(varchar(10),  DATEPART(QUARTER, fecha)) Trimestre_Name
FROM x 
OPTION(MAXRECURSION 0)
0

好的..试试这个: http://www.devshed.com/c/a/MySQL/Delving-Deeper-into-MySQL-50/
http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html
http://www.roseindia.net/sql/mysql-example/mysql-loop.shtml

使用它来生成一个临时表，然后在临时表上执行select *。或者逐个输出结果。
你说你想做的事情不能用SELECT语句完成，但是可能可以使用特定于MySQL的东西来完成。
再说一遍，也许你需要使用游标: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

0
WITH
  Digits AS (SELECT 0 D UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
  Dates AS (SELECT adddate('1970-01-01',t4.d*10000 + t3.d*1000 + t2.d*100 + t1.d*10 +t0.d) AS date FROM Digits AS t0, Digits AS t1, Digits AS t2, Digits AS t3, Digits AS t4)
SELECT * FROM Dates WHERE date BETWEEN '2017-01-01' AND '2017-12-31'
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接