LiveCode Forums.

Please can somebody help me with storing AND performing "date arithmetic".

I have found that storing the system date may vary from computer to computer so this is not ideal. I thought of storing the value as seconds or dateitems but when performing lookups on the DB, I get conflicting results and am just not sure what is he best method to use.

Anyway information and advice will be very well received.
Thanks
N

Hi Nico,

too few information!

What are you trying to do?
What did you try so far?
Any script you'd like to show?
What "conflicting" results do you get?
When do you get them?


Best

Klaus

nicoloose wrote:I have found that storing the system date may vary from computer to computer so this is not ideal. I thought of storing the value as seconds or dateitems but when performing lookups on the DB, I get conflicting results and am just not sure what is he best method to use.

You have to separate both issues :
-the ways LiveCode can perform "date arithmetic"

-and the ways your database perfom the same tasks and/or "date presentation" (the ways MYSQL server for instance can display a timestamp column)

-> a whole range of technical solutions

Questions :
-what do you try to achieve ? Example of queries please
-which database server do you use ?

Thank you for the replies.

I am using an sqlite database and performing all operations using the DBLib script so the sql queries are handled pretty much by that. Obviously livecode can convert a date to many different formats, dateitems, seconds, short english date, etc, etc... but I haven't yet found an effective means of storing a date and then querying the DB and having an honest result returned. For example, I have some records in a sales table with a salesdate field where dates are stored using the short english date format. I have created a search form where I give the user a start and end date (range). If I choose "1/1/13" as a start date and "28/1/13" as an end date I would expect ALL sales that have a sale date in January 2013 to be returned but I only ever get the last 3 records. The date field in the DB is in exactly the same format as the query.

SELECT * FROM sales WHERE saleDate >= "1/1/13" AND saleDate <= "1/31/13" (Returns last 3 records)
SELECT * FROM sales WHERE salesDate >= "1/6/13" AND salesDate <= "1/9/13" (Returns 4 records that fall between the 7th and the 9th)

So my question is, is it better to store the dates as seconds, dateitems or another format that is "easier" to perform "date arithmetic"?

nicoloose wrote:Thank you for the replies.

I am using an sqlite database and performing all operations using the DBLib script so the sql queries are handled pretty much by that. Obviously livecode can convert a date to many different formats, dateitems, seconds, short english date, etc, etc... but I haven't yet found an effective means of storing a date and then querying the DB and having an honest result returned. For example, I have some records in a sales table with a salesdate field where dates are stored using the short english date format. I have created a search form where I give the user a start and end date (range). If I choose "1/1/13" as a start date and "28/1/13" as an end date I would expect ALL sales that have a sale date in January 2013 to be returned but I only ever get the last 3 records. The date field in the DB is in exactly the same format as the query.

SELECT * FROM sales WHERE saleDate >= "1/1/13" AND saleDate <= "1/31/13" (Returns last 3 records)
SELECT * FROM sales WHERE salesDate >= "1/6/13" AND salesDate <= "1/9/13" (Returns 4 records that fall between the 7th and the 9th)

So my question is, is it better to store the dates as seconds, dateitems or another format that is "easier" to perform "date arithmetic"?

From my understanding you can not store a "short english date" in a SQLLITE date column.

Look, you 3 date time datatype :
http://www.sqlite.org/datatype3.html

The regular date format for SQL is YYYY-MM-DD

However, all SQL DB can "modify" the display of dates (so can retrieve virtually all the format you want from a SELECT) using various functions

Look at :
http://www.sqlite.org/lang_datefunc.html

So to summarize :
-to make a SELECT, livecode needs to speak "SQL date" :
SELECT * FROM sales WHERE salesDate >= '2013-10-01'
SELECT * FROM sales WHERE salesDate >= '2013-10-01 00:00:01'

-but then you can choose the format for display :
like (MySQL) :
SELECT date_format(salesDate,'%Y/%m/%d') from sales WHERE salesDate >= '2013-10-01'

From my understanding you can not store a "short english date" in a SQLLITE date column.

Many thanks for these insightful suggestions. I have changed my field type in the sqlite database to an integer and then stored the "English date" converted to seconds. I perform my query based on this integer value and it is returning the correct results.

Thanks again.
Regards
Nic