importing date from text file and storing in sqlite

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

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

importing date from text file and storing in sqlite

Post by nicoloose » Wed Dec 04, 2019 10:55 pm

I am writing a little app where I import my data from my Amazon sales and store everything in an sqlite database for further analysis. I am having trouble with storing the dates. I have searched this forum and I find the answers a little ambiguous and confusing.

My date comes in the following format: 30 Nov 2019 so I convert it into dateitems format YYYY-MM-DD HH:MM:SS so it will look like this 2019-11-30 00:00.

For some reason, it is stored in the DB as such: 2019_11_30_00_00. Why?

How do I store this in SQLite? Some are saying convert the date to seconds and store as integer, some say convert to dateitems and store as text.

When running select statements for my dashboards, I want to find transactions within custom date periods (start and end date) and return data grouped by month.

If I can figure out the data storage then I am sure I can work out the rest.

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9579
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: importing date from text file and storing in sqlite

Post by dunbarx » Thu Dec 05, 2019 12:26 am

Hi.

I cannot help you with any funny stuff going on with sqLite, but are you sure you are massaging the string:

Code: Select all

30 Nov 2019
properly? I ask because you have to be doing something, since the convert command will not transform that string into dateItems without something else going on.

Unless when you said "convert", you really meant your own machinations.

Craig

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: importing date from text file and storing in sqlite

Post by FourthWorld » Thu Dec 05, 2019 3:01 am

nicoloose wrote:
Wed Dec 04, 2019 10:55 pm
For some reason, it is stored in the DB as such: 2019_11_30_00_00. Why?
Is that what's physically stored in the DB file, or what you see when you retrieve the value from the DB?

There may not be a difference. But there might be. And the difference may help us understand the root of the problem.

Without seeing any code for how data is put into the DB, and how it's retrieved from the DB, it may be difficult to guess what's going on.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

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

Re: importing date from text file and storing in sqlite

Post by nicoloose » Thu Dec 05, 2019 9:57 am

Hi Richard thanks for the reply. I am converting the date as such:

Code: Select all

switch tTemp
            case "sd_date"
               put word 1 of theRec into tDay
               put word 2 of theRec into tMonth
               put word 3 of theRec into tYear
               switch tMonth
                  case "Jan"
                     put "01" into tMonth
                     break
                  case "Feb"
                     put "02" into tMonth
                     break
                  case "Mar"
                     put "03" into tMonth
                     break
                  case "Apr"
                     put "04" into tMonth
                     break
                  case "May"
                     put "05" into tMonth
                     break
                  case "Jun"
                     put "06" into tMonth
                     break
                  case "Jul"
                     put "07" into tMonth
                     break
                  case "Aug"
                     put "08" into tMonth
                     break
                  case "Sep"
                     put "09" into tMonth
                     break
                  case "Oct"
                     put "10" into tMonth
                     break
                  case "Nov"
                     put "11" into tMonth
                     break
                  case "Dec"
                     put "12" into tMonth
                     break
               end switch
               put tYear & "-" & tMonth & "-" & tDay into theRec
               get sqlDate(theRec) //Convert the date into correct format
               put it into theRec
               break
With the convert function below

Code: Select all

function sqlDate pDate
   convert pDate to dateitems
   set the numberformat to "00"
   return merge("[[item 1 of pDate]]-[[item 2 of pDate + 0]]-[[item 3 of pDate + 0]]")
end sqlDate
When I query the records in sqlite, any records that had a "-" in them now have "_". This is not specific to dates.

I use Levure Application Framework.

bn
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3990
Joined: Sun Jan 07, 2007 9:12 pm
Location: Bochum, Germany

Re: importing date from text file and storing in sqlite

Post by bn » Thu Dec 05, 2019 12:14 pm

Hi nicoloose,

There are some problems with your code regarding the date format you pass.

After your switch statement you do

Code: Select all

put tYear & "-" & tMonth & "-" & tDay into theRec
that is not a recognized format for date in LC (see date in dictionary)

change that to

Code: Select all

put  tMonth & "/" & tDay & "/" & tYear into theRec
which is the date format of LC

in your function are further problems

Code: Select all

function sqlDate pDate
   convert pDate to dateitems -- fails because of false date format
   set the numberformat to "00"
   return merge("[[item 1 of pDate]]-[[item 2 of pDate + 0]]-[[item 3 of pDate + 0]]")
end sqlDate
since your conversion of pDate to dateItems fails because of the wrong format the merge also does not do what you expect because there are not commas as delimiters in pDate, just "-".

change that function to

Code: Select all

function sqlDate pDate
   convert pDate to dateItems
   set the numberformat to "00"
   return merge("[[item 1 of pDate]]-[[item 2 of pDate + 0]]-[[item 3 of pDate + 0]] [[item 4 of pDate + 0]]:[[item 5 of pDate + 0]]")
end sqlDate
This will return for your example date
30 Nov 2019
2019-11-30 00:00

However what happens in your sqlite database with this I don't know anything about.

May be you should use the debugger to check that your variables contain what you actually expect.

Kind regards
Bernd

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: importing date from text file and storing in sqlite

Post by Klaus » Thu Dec 05, 2019 1:56 pm

Yeah, what Bernd said! :-)

A hint for the future:

Code: Select all

switch tTemp
      case "sd_date"
         put word 1 of theRec into tDay
         put word 2 of theRec into tMonth
         put word 3 of theRec into tYear
         
         ## We have everything already in LC:
         ## "the weekdaynames", "the monthnames", "the system monthnames" etc.
         ##  and even "the abbreviated monthnames"
         ## We use this to save A LOT OF TYPING!
         put lineoffest(tMonth,the abbr monthnames) into tMonthNumber
         
         ## Force leading ZERO:
         put format("%02d",tMonthNumber) into tMonth
               
         put tYear & "-" & tMonth & "-" & tDay into theRec
         get sqlDate(theRec) //Convert the date into correct format
         put it into theRec
         break
...

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

Re: importing date from text file and storing in sqlite

Post by nicoloose » Thu Dec 05, 2019 2:31 pm

Gentlemen, thank you both for your input!

Klaus, that's a nice shortened version of what I was trying to do... easy when you know how.. :wink:

I build an array prior to creating a record in the DB and the array looks like:
Screenshot 2019-12-05 at 13.27.11.png
Everything looks normal but once in the DB, all hyphens convert to underscores. This makes things tricky when searching DB. By the way, what datatype should the date be in? TEXT?
Screenshot 2019-12-05 at 13.29.25.png

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: importing date from text file and storing in sqlite

Post by Klaus » Thu Dec 05, 2019 2:59 pm

nicoloose wrote:
Thu Dec 05, 2019 2:31 pm
... easy when you know how.. :wink:
Yes, I knew how, so I am telling you! :-)

bn
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3990
Joined: Sun Jan 07, 2007 9:12 pm
Location: Bochum, Germany

Re: importing date from text file and storing in sqlite

Post by bn » Thu Dec 05, 2019 4:28 pm

nicoloose wrote:
Thu Dec 05, 2019 2:31 pm
By the way, what datatype should the date be in? TEXT?
That is really a sqlite-question.
a quick google search for "sqlite date format" turns up useful information.
Kind regards
Bernd

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7215
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: importing date from text file and storing in sqlite

Post by jacque » Thu Dec 05, 2019 6:09 pm

I know almost nothing about databases but whenever I need to query one for a date, my database guy says it has to be in this format:

20191130

For this, you can use a date field in the database. If you want to store it as you do now then I assume it must be a text field. But my guess is that using SQL native date format will allow the database to find dates and date ranges more accurately.

Edit : I just looked at the link Bernd posted and those dates use hyphens, so maybe I'm wrong. Maybe my database guy is translating my input to use hyphens.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

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

Re: importing date from text file and storing in sqlite

Post by nicoloose » Thu Dec 05, 2019 7:36 pm

I have decided to store as seconds in an integer data type.

Now just to try and figure out why we have underscores all over the place!

Post Reply

Return to “Getting Started with LiveCode - Experienced Developers”