Page 1 of 1

importing date from text file and storing in sqlite

Posted: Wed Dec 04, 2019 10:55 pm
by nicoloose
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.

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 12:26 am
by dunbarx
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

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 3:01 am
by FourthWorld
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.

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 9:57 am
by nicoloose
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.

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 12:14 pm
by bn
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

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 1:56 pm
by Klaus
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
...

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 2:31 pm
by nicoloose
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

Re: importing date from text file and storing in sqlite

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

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 4:28 pm
by bn
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

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 6:09 pm
by jacque
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.

Re: importing date from text file and storing in sqlite

Posted: Thu Dec 05, 2019 7:36 pm
by nicoloose
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!