Result

Answer 2

The answer below is based on the information provided in this tutorial. For this example, let's assume we have a table KenyaWP with a column time that combines information on date and time, like: '2011/11/01 5:49:41+00'.

What we need is the SUBSTR function to extract the date and the time. The syntax is as follows:

If the start location is a positive integer X then the substring will begin X number of characters from the left of the string. If the start location is a negative integer then the substring will begin X number of characters from the right. Thus, to extract a column with only the data from the field time in the table use the following:

SELECT SUBSTR(time,1,10) FROM KenyaWP

The time is slightly more complicated as the length of the time string varies (see e.g., 9:10:26+00 vs 12:10:26+00). First step is the same as above

SELECT SUBSTR(time,12,8) FROM KenyaWP

This extracts the time, excluding the +00. Now I need to remove the excess whitespace, for which I can use the sqlite trim(X,Y) function. The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X. I can combine this function with the one above to extract and at the same time remove white space:

SELECT TRIM(SUBSTR(time,12,8)) FROM KenyaWP

These queries just produces a temporary view of course. We need to add one new column for the data. We will use the already existing time column for the time.