importing date from text file and storing in sqlite
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
importing date from text file and storing in sqlite
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.
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.
-
- VIP Livecode Opensource Backer
- Posts: 9648
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: importing date from text file and storing in sqlite
Hi.
I cannot help you with any funny stuff going on with sqLite, but are you sure you are massaging the string:
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
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
Unless when you said "convert", you really meant your own machinations.
Craig
-
- VIP Livecode Opensource Backer
- Posts: 9823
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: importing date from text file and storing in sqlite
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
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
Re: importing date from text file and storing in sqlite
Hi Richard thanks for the reply. I am converting the date as such:
With the convert function below
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.
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
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
I use Levure Application Framework.
-
- VIP Livecode Opensource Backer
- Posts: 3999
- Joined: Sun Jan 07, 2007 9:12 pm
- Location: Bochum, Germany
Re: importing date from text file and storing in sqlite
Hi nicoloose,
There are some problems with your code regarding the date format you pass.
After your switch statement you dothat is not a recognized format for date in LC (see date in dictionary)
change that towhich is the date format of LC
in your function are further problems
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
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
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
change that to
Code: Select all
put tMonth & "/" & tDay & "/" & tYear into theRec
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
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
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
Yeah, what Bernd said!
A hint for the future:
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
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..
I build an array prior to creating a record in the DB and the array looks like:
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?
Klaus, that's a nice shortened version of what I was trying to do... easy when you know how..
I build an array prior to creating a record in the DB and the array looks like:
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?
-
- VIP Livecode Opensource Backer
- Posts: 3999
- Joined: Sun Jan 07, 2007 9:12 pm
- Location: Bochum, Germany
Re: importing date from text file and storing in sqlite
That is really a sqlite-question.
a quick google search for "sqlite date format" turns up useful information.
Kind regards
Bernd
-
- VIP Livecode Opensource Backer
- Posts: 7228
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: importing date from text file and storing in sqlite
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.
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
HyperActive Software | http://www.hyperactivesw.com
Re: importing date from text file and storing in sqlite
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!
Now just to try and figure out why we have underscores all over the place!