日期排序和基于当前日期的检索结果(出生日期列表) SQLite[英] Ordering of Dates and retrieving results based on current date (Birth Day List) SQLite

本文是小编为大家收集整理的关于日期排序和基于当前日期的检索结果(出生日期列表) SQLite的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个日期存储在sqlite列中,该格式为yyyy-mm-dd(不分为三列,每年的日期和月份),现在我需要进行查询,从而为我提供结果.最接近查询作为第一个条目并按升序订单进行的日期.在这里,我对年度,每月和日期不感兴趣.根据我的要求,我应该处理哪种类型的查询,以便按顺序获得这些日期.

示例:

我有日期

1980-01-11,1999-08-03,2013-12-03 查询后,我应该得到:(根据当前日期,考虑到2013年12月1日)

2013-12-03,1999-01-11,11,1980-08-03(仅基于月和一天的排序).

我还从这里获取参考: mysql query query根据当前日期对即将到来的生日进行排序,但还不清楚.我认为unix时间戳是在日期列中存储的长度值,将有助于我感兴趣的查询类型,而不是简单的日期格式.

推荐答案

以下会做:

SELECT *
FROM _table
ORDER BY SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6), SUBSTR(birthdate, 6)

sql小提琴.

SUBSTR(date, 6)将从日期开始删除年份.必须在MM-DD中对生日进行分类,但首先应向今天发生的情况显示出来,然后再显示那些在今天之前发生的.

SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6)将返回0的生日MM-DD MM-DD MM-DD,否则1.因此,首先表达将在开始日期后的过去日期.第二个参数仅ORDER MM-DD.

日期

请参阅 sql小提琴.

其他推荐答案

有很多方法可以做到这一点,有些方法比其他方法更有效.我会建议一个可以在不更改数据库格式的情况下有效的解决方案,但是请注意,如果您可以使用单独的年度,月,日,一日整数列或UNIX时间戳列.

select *, 
strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', birthday) || '-' || 
    strftime('%d', birthday)
)
- strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', 'now') || '-' || 
    strftime('%d', 'now')
)
as daydiff
from users
where daydiff >=0

union all

select *, 
strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', birthday) || '-' || 
    strftime('%d', birthday)
)
- strftime('%j', 
    strftime('%Y', 'now','+1 year') || '-' || 
    strftime('%m', 'now') || '-' || 
    strftime('%d', 'now')
) + 366
as daydiff
from users
where daydiff <366
order by daydiff

上述今天的年度和每个用户每月和日期来计算一年中的一天(例如,2013年12月11日今天是第345天),并减去了今天日期的一年.本年度发生的生日将具有延迟价值> = 0,因此我们首先使用它们.这是union的第一部分.

第二部分进行相同的计算,但对于那些生日那些是明年的,所以我们向Daydiff的价值添加366,并确保我们只获得第一部分中没有得到的字段.

可以用CASE WHEN而不是union重写相同的查询.案例替代方案会更快,因为它只会从用户表中获取一行,而不是两次,但是在这个论坛上写作真的很难.第二想,我还是写它,因为它更快

select *, 
CASE WHEN 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) >= 0
THEN 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) 
ELSE 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) + 366 
END
as daydiff
from users
order by daydiff

也是最后一个注释.我在明年的生日中手动添加366个,但正确的方法是根据年份(365或366)添加一年中的日子.由于我们只需要订购,因此不会造成麻烦,因为最坏的情况是,它将为所有明年的用户添加一个.因此,在" 2013-12-31"上的生日将使Daydiff = 20,但在'2014-01-01'的生日将使Daydiff = 22.

编辑:

这是 fiddle

本文地址:https://www.itbaoku.cn/post/102550.html

问题描述

I have a date stored as string in a Sqlite column, the format is YYYY-MM-DD ( Not separated into three columns for year date and month) Now I have a requirement to make a query which gives me results with the closest date to the date the query is being made as the first entry and the order goes on in ascending order.Here I am not interested in the year, only the month and the day matters. As per my requirement, what type of query should I process so that I get these dates in order.

Example:

I have the dates

1980-01-11, 1999-08-03, 2013-12-03 After the Query I should get:(Based on current date, considering it is December 1 2013)

2013-12-03,1999-01-11,1980-08-03 (Sort based only on month and day).

I also took reference from here :MySQL query to sort upcoming birthdays based on current date But it is not much clear. I think Unix timestamps as long values stored in date column would aid the type of query I am interested in, rather than the simple date format.

推荐答案

Following will do:

SELECT *
FROM _table
ORDER BY SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6), SUBSTR(birthdate, 6)

Check it in SQL Fiddle.

SUBSTR(date, 6) will remove year part from date. Birthdates must be sorted in MM-DD, but first should be shown those which will happen after today, and then those which happen before today.

SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6) will return 0 for birthdates MM-DD greater or equal to today MM-DD, 1 otherwise. So, first expression will ORDER past dates after comming date. Second parameter just ORDER dates by MM-DD.

See step by step in SQL Fiddle.

其他推荐答案

There are quite a few ways to do it, some more efficient than others. I will suggest a solution that can be effective without changing your database format, but note that it can all get much faster (especially if we are talking about a big birthday list) if you can use separate Year,Month,Day integer columns, or a unix timestamp column.

select *, 
strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', birthday) || '-' || 
    strftime('%d', birthday)
)
- strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', 'now') || '-' || 
    strftime('%d', 'now')
)
as daydiff
from users
where daydiff >=0

union all

select *, 
strftime('%j', 
    strftime('%Y', 'now') || '-' || 
    strftime('%m', birthday) || '-' || 
    strftime('%d', birthday)
)
- strftime('%j', 
    strftime('%Y', 'now','+1 year') || '-' || 
    strftime('%m', 'now') || '-' || 
    strftime('%d', 'now')
) + 366
as daydiff
from users
where daydiff <366
order by daydiff

The above uses today's year and each users month and day parts to calculate day of year (e.g. today Dec 11, 2013 is day 345) and subtracts the day of year of today's date. Birthdays that occur in the current year will have a daydiff value >= 0, so we use them first. This was the first part of the union.

The second part makes the same calculation but for those whose birthday is next year, so we add 366 to the daydiff value and make sure we only get the fields that we didn't get from the first part.

The same query can be rewritten with a CASE WHEN instead of union. The CASE alternative will be faster because it will only fetch the rows from the users table just once, instead of twice, but it would be really ugly for me to write in this forum. On second thought, I'll write it anyway, since it is faster

select *, 
CASE WHEN 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) >= 0
THEN 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) 
ELSE 
  strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday)
  )
  - strftime('%j', 
    strftime('%Y', 'now') || '-' || strftime('%m', 'now') || '-' || strftime('%d', 'now')
  ) + 366 
END
as daydiff
from users
order by daydiff

Also a last note. I manually add 366 on next years birthdays, but the correct thing would be to add the days of year depending on the year (365 or 366). Since we only need it for ordering, this will not cause trouble because the worst case is that it will add one to daydiff for all next year's users. So birthday on '2013-12-31' will give daydiff=20, but birthday on '2014-01-01' will give daydiff=22.

EDIT:

Here's a fiddle

相关标签/搜索