How to calculate number of days

General
1

Long time since I have been here… new project I am working on for fun. I have a simple tracking system I use to track my income and expenses (it’s set to work for me so it’s a personal project). Anyhow I want to add a feature where I can export a range of data and I am stumped as to the most effective way to do it.

The data fields are as follows:
RecordNumber
DateOf
PmntDepSrc
WithDrawAmt
DepositAmt
AcctBalance

The issue I am running into is when I select a date range I am trying to get an accurate return to write out. The date format I use is YYYY-MM-DD. When I say write out I am referring to writing the data to a file on my drive for use external to my application.

Example: I want to write out records from 2021-11-18 through 2022-01-03. The issue I am running into is getting all the records in between these dates as well. I have some ideas but they are not very efficient in that it is going to be pretty large. I know there is an efficient way to do this… I just can’t ken it.

2

Where are these data fields? In a database? A listbox? And where do you want to write them out to? And how are you selecting the records to write out? Telling us the platform and version of Xojo might help too.

1 Like
3

if you are using sqlite this can help.
https://www.sqlite.org/lang_datefunc.html
https://www.sqlite.org/datatype3.html#type_affinity

4

Stored in a database - field ID = DateOf
Also listed in a list box.

The dates are selected by entering a start and end date then execute a query to get the data in the range. From there it’s written out as a .csv file for use later.

Platform is Linux. I already know how to write the file from the data, what I am having an issue with is getting all the data efficiently between the dates selected.

5

Hmm, I came up with a quick solution that is not overly cumbersome. I can use a Julian calendar to solve my problem! Thanks!

6

the select with a sqlite should be something like
select * from table1 where Date(DateOf)>=Date('2021-11-18') and Date(DateOf)<=Date('2022-01-03')

other databases have a date type like timestamp.
some db’s offer a query with between

and Date between '2011/02/25' and '2011/02/27'

each db system have its own features.

1 Like
7

Asking for help and then not sharing your solution. Why? :slight_smile:

8

Personally when using an SQLite database I store all my dates as SecondsSince1970, since this is the data storage layer, not the presentation layer. That makes manipulations based on dates that much easier.

1 Like
9

Because I haven’t written it yet… I have a day job so I get small bits of time here and there to work on my projects.

1 Like
10

I’ll try this… quicker and easier than the solution I have in my noggin.

11

With the seconds since 1970. Isn’t that going to break in 2038? I mean, at least for right now? Or have they solved that issue and i’m just unaware of it?

12

I also store my dates in SQLDate format. Can’t you just do a string compare? eg

SELECT * FROM TableName WHERE DateOf >= ‘2021-11-18’ AND DateOf <= ‘2022-01-03’

If all the data is in the proper SQLDate format, then it should only select dates within that range.

1 Like
13

Only if you are stuck with 32-bit arithmetic.