Help with Dates

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
nicoloose
Posts: 99
Joined: Mon Sep 16, 2013 3:35 pm

Help with Dates

Post by nicoloose » Wed Oct 09, 2013 11:10 am

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

Klaus
Posts: 14199
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Help with Dates

Post by Klaus » Wed Oct 09, 2013 11:59 am

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?
8)

Best

Klaus

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Help with Dates

Post by bangkok » Wed Oct 09, 2013 2:04 pm

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 ?

nicoloose
Posts: 99
Joined: Mon Sep 16, 2013 3:35 pm

Re: Help with Dates

Post by nicoloose » Thu Oct 10, 2013 8:47 am

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"?

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Help with Dates

Post by bangkok » Thu Oct 10, 2013 9:12 am

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'

nicoloose
Posts: 99
Joined: Mon Sep 16, 2013 3:35 pm

Re: Help with Dates

Post by nicoloose » Thu Oct 10, 2013 2:19 pm

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

Post Reply