英文:
How to build a sql query to get 100 rows belonging to weekday(Mon-Fri) from a table using epoch time column
问题
我想要查询一个Oracle SQL数据库，以获取属于工作日（星期一至星期五）的100行数据（这个数字可以增加到10倍），使用epoch时间列。查询将从一个Spring Boot应用程序中执行。构建这个要求的逻辑和查询的方法是什么？
考虑下面的表格作为示例。A和B的日期都落在星期四（即工作日），但C的日期在将epoch时间转换后是星期六（根据GMT时间）。因此，输出必须是行A和B。
| Name | time_c. |
|---|---|
| A | 1674086400 |
| B | 1673481600 |
| C | 1673049600 |
我正在检查是否可以在将epoch时间转换为日期后使用SQL的TO_CHAR()函数，但我不确定如何在查询中应用筛选条件。
英文:
I want query a oracle sql database to get 100(this number can increase 10x) rows belonging to weekday (Mon-Fri) using epoch time column. Query will be executed from a spring boot application. What is the approach for building the logic & query for this requirement?
Consider below table for example. The day for A & B both falls on Thursday(i.e weekday) But C falls on Saturday when the epoch time is converted. (acc to GMT time.).
Hence the output must be the rows A & B.
| Name | time_c. |
|---|---|
| A | 1674086400 |
| B | 1673481600 |
| C | 1673049600 |
I was checking if the sql TO_CHAR() function can some how be used after converting the epoch time to date but I'm not sure how the filter can be applied within the query.
答案1
得分: 1
有一周7天，1970-01-01是星期四，所以如果我们从1970-01-01开始按照7天的周期计数（其中1970-01-01是一周的第0天），那么周末是每周的第2和第3天（从零开始计数）。
您可以使用这个方法仅使用算术运算来排除周末：
SELECT *
FROM table_name
WHERE MOD(time_c, 7 * 24 * 60 * 60) < 2 * 24 * 60 * 60
OR MOD(time_c, 7 * 24 * 60 * 60) >= 4 * 24 * 60 * 60
FETCH FIRST 100 ROWS ONLY;
英文:
There are 7 days a week and 1970-01-01 is a Thursday, so if we start counting from 1970-01-01 in 7-day periods (where 1970-01-01 is the 0th day of the week) then the weekends are the 2rd and 3th days (again zero-indexed) of each week.
You can use that to exclude the weekends using only arithmetic:
SELECT *
FROM table_name
WHERE MOD(time_c, 7 * 24 * 60 * 60) < 2 * 24 * 60 * 60
OR MOD(time_c, 7 * 24 * 60 * 60) >= 4 * 24 * 60 * 60
FETCH FIRST 100 ROWS ONLY;
答案2
得分: 0
至于Oracle，这里有一个选项。
（仅设置日期格式以显示“是什么”；您不必这样做）
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
会话已更改。
示例数据：
SQL> with test (name, time_c) as
2 (select 'A', 1674086400 from dual union all
3 select 'B', 1673481600 from dual union all
4 select 'C', 1673049600 from dual
5 ),
查询将time_c转换为date数据类型的值（datum列），另外还找到了它是哪一天（dan列）。然后，排除周末并获取任意数量的行（第16行）：
6 temp as
7 (select name, time_c,
8 date '1970-01-01' + time_c / 86400 datum,
9 to_char(date '1970-01-01' + time_c / 86400, 'dy', 'nls_date_language = english') dan
10 from test
11 )
12 select name, time_c, datum, dan
13 from temp
14 where dan not in ('sat', 'sun')
15 order by datum desc
16 fetch first 100 rows only;
N TIME_C DATUM DAN
- ---------- ---------- ------------
A 1674086400 19.01.2023 thu
B 1673481600 12.01.2023 thu
SQL>
这段代码可以重写为一个函数，该函数返回例如refcursor或您认为合适的内容，以便您可以在应用程序中使用它。
英文:
As far as Oracle is concerned, here's one option.
(setting date format just to show what is what; you don't have to do that)
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
Sample data:
SQL> with test (name, time_c) as
2 (select 'A', 1674086400 from dual union all
3 select 'B', 1673481600 from dual union all
4 select 'C', 1673049600 from dual
5 ),
Query converts time_c to a date datatype value (datum column) and - additionally - finds which day it was (the dan column). Then, exclude weekends and fetch as many rows as you want (line #16):
6 temp as
7 (select name, time_c,
8 date '1970-01-01' + time_c / 86400 datum,
9 to_char(date '1970-01-01' + time_c / 86400, 'dy', 'nls_date_language = english') dan
10 from test
11 )
12 select name, time_c, datum, dan
13 from temp
14 where dan not in ('sat', 'sun')
15 order by datum desc
16 fetch first 100 rows only;
N TIME_C DATUM DAN
- ---------- ---------- ------------
A 1674086400 19.01.2023 thu
B 1673481600 12.01.2023 thu
SQL>
That piece of code can be rewritten to a function that returns e.g. refcursor or whatever you find appropriate so that you could use it in your application.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论