-1

I have a sqllite table which contains a datetime col of dates and col of real numbers. Any ideas on how i can query to get all all the years within that date range

Note the dates are stored as yyyy-mm-dd For example if I had a table with all the dates from Jan 1 1990 - Dec 31 2021 and I wanted between Feb 1 and Feb 2 I would get

2022-02-01
2022-02-02
2021-02-01
2021-02-02
... 
1991-02-01
1991-02-02
1990-02-01
1990-02-02

or if i query Jan 31 to Feb 2 I would get

2022-01-31
2022-02-01
2022-02-02
2021-01-31
2021-02-01
2021-02-02
... 
1991-01-31
1991-02-01
1991-02-02
1991-01-31
1991-02-01
1991-02-02

I'm trying to do this using peewee for python but I'm not even sure how to write this sql statement. From what I've seen sql has a between statement but this wont work as it would give me only one year when I want every record in the database between a given range.

B4dmonkey
  • 112
  • 1
  • 2
  • 12
  • Do you really store dates in this format: `MMM-D-YYYY`? – forpas Jan 08 '23 at 08:01
  • *The current answers assume that my data is stored wrong in the db* maybe it is because your sample data is in the wrong format. – forpas Jan 11 '23 at 21:31
  • no the dates are in the format yyyy-mm-dd, theyre stored as datetime not a string – B4dmonkey Jan 11 '23 at 21:36
  • There is no datetime data type in SQLite: [Datatypes In SQLite](https://www.sqlite.org/datatype3.html). Your dates are strings. If they have the proper format why did you post sample data with a different format? – forpas Jan 11 '23 at 21:39
  • I'm using an ORM PeeWee. The underlying db is sqlite although it doesn't have to be. It does have to be some SQL db however and from my understanding sql the statements are generally interchangeable between db (with some exceptions) I wrote it in that format because i thought it was more readable but apparently its causing more confusion than the root question of finding dates for multiple years based on only the month and the day. – B4dmonkey Jan 11 '23 at 21:46
  • 1
    Date arithmetic and functions are generally different between different databases. Don't expect the same code that works in SQLite to work also in any other database. Also, if Parfait's answer does not solve your problem then your description of the problem is not clear. Create a fiddle: https://dbfiddle.uk/vRbqFpk_ with sample data so that we can reproduce your issue. – forpas Jan 11 '23 at 21:50
  • I believe I've answered your question. I think the confusion was due to your sample data being in a weird format, and it being somewhat unclear what exactly you were hoping to accomplish. – coleifer Jan 12 '23 at 13:48

4 Answers4

0

Consider filtering query by building dates with date() keeping year of current date. Date ranges across years may need to be split with a self-join or union:

SELECT * 
FROM my_table
WHERE my_date BETWEEN date(strftime('%Y', my_date) ||'-01-31')
                  AND date(strftime('%Y', my_date) ||'-02-02')
Parfait
  • 104,375
  • 17
  • 94
  • 125
0

Since it appears that the author's sample data posted originally was not representative of the actual data, and the ACTUAL data is stored in YYYY-MM-DD, then it is quite simple using sqlite's strftime():

start = '01-31'
end = '02-02'
query = (Reg.select()
         .where(fn.strftime('%m-%d', Reg.date).between(start, end)))
for row in query:
    print(row.date)

Should print something like:

2018-01-31
2018-02-01
2018-02-02
2019-01-31
...
coleifer
  • 24,887
  • 6
  • 60
  • 75
-1

To get the years within a date range in a SQLite table, you can use the strftime function to extract the year from the date column and then use the distinct keyword to only return unique years.

SELECT DISTINCT strftime('%Y', date_column) as year
FROM table_name
WHERE date_column BETWEEN '2022-01-01' AND '2022-12-31'
chirath bandara
  • 9
  • 2
  • I'm don't think this work when you put ``` WHERE date_column BETWEEN '2022-01-01' AND '2022-12-31' ``` I would only get the dates in the year 2022 and none of the previous years – B4dmonkey Jan 08 '23 at 03:56
-1

I accidentally ran into this solution (it was a problem for me) working in SQL Server, where I was trying to get records created in the last week;

SELECT *
FROM table1
WHERE DATEPART(WK, table1.date_entered) = DATEPART (WK, GETDATE()) -1

This returns everything that was created in Week now-1 of all previous years, similar to the SQLite strftime(%W, my-date-here).

If the dates you are querying don't span over a change in years (i.e; not Dec-29 --> Jan-5) you could do something like the below;

 SELECT date_column
 FROM myTable
 WHERE strftime($j, date_column) >= strftime($j, **MY_START_DATE**)
   AND strftime($j, date_column) <= strftime($j, **MY_END_DATE**)

Here we get the Day of the year (thats what $j gives us) and select anything from date_column where the day of the year is between our start and end dates. If i understood your question properly, it will give you as listed

2022-02-01 
2022-02-02 
2021-02-01 
2021-02-02 
...

IF you want further information on working with dates I can't do better than refer you to "Robyn Page’s SQL Server DATE/TIME Workbench". Obviously this article is on SQL Server but most of it translates to other DB's.

Sand-
  • 1
  • 2