First, create a new table named datetime_real . Second, insert the “current” date and time value into the datetime_real table. We used the julianday() function to convert the current date and time to the Julian Day. Third, query data from the datetime_real table.
SQLite time() function accepts a time string and one or more modifiers. It returns a string that represents a specific time in this format: HH:MM:SS . The following shows the syntax of the time() function: time(time_string[, modifier, ...])
The date() function returns the date as text in this format: YYYY-MM-DD. The time() function returns the time as text in this format: HH:MM:SS. The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS.
As Sqlite doesn't have a date type you will need to do string comparison to achieve this. For that to work you need to reverse the order - eg from dd/MM/yyyy to yyyyMMdd, using something like
where substr(column,7)||substr(column,4,2)||substr(column,1,2)
between '20101101' and '20101130'
Saved date as TEXT( 20/10/2013 03:26 ) To do query and to select records between dates?
Better version is:
SELECT TIMSTARTTIMEDATE
FROM TIMER
WHERE DATE(substr(TIMSTARTTIMEDATE,7,4)
||substr(TIMSTARTTIMEDATE,4,2)
||substr(TIMSTARTTIMEDATE,1,2))
BETWEEN DATE(20131020) AND DATE(20131021);
the substr from 20/10/2013 gives 20131020 date format DATE(20131021) - that makes SQL working with dates and using date and time functions.
OR
SELECT TIMSTARTTIMEDATE
FROM TIMER
WHERE DATE(substr(TIMSTARTTIMEDATE,7,4)
||'-'
||substr(TIMSTARTTIMEDATE,4,2)
||'-'
||substr(TIMSTARTTIMEDATE,1,2))
BETWEEN DATE('2013-10-20') AND DATE('2013-10-21');
and here is in one line
SELECT TIMSTARTTIMEDATE FROM TIMER WHERE DATE(substr(TIMSTARTTIMEDATE,7,4)||'-'||substr(TIMSTARTTIMEDATE,4,2)||'-'||substr(TIMSTARTTIMEDATE,1,2)) BETWEEN DATE('2013-10-20') AND DATE('2013-10-21');
One thing you should look into is the SQLite date and time functions, especially if you're going to have to manipulate a lot of dates. It's the sane way to use dates, at the cost of changing the internal format (has to be ISO, i.e. yyyy-MM-dd).
The UDF approach is my preference compared to brittle substr
values.
#!/usr/bin/env python3
import sqlite3
from dateutil import parser
from pprint import pprint
def date_parse(s):
''' Converts a string to a date '''
try:
t = parser.parse(s, parser.parserinfo(dayfirst=True))
return t.strftime('%Y-%m-%d')
except:
return None
def dict_factory(cursor, row):
''' Helper for dict row results '''
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
def main():
''' Demonstrate UDF '''
with sqlite3.connect(":memory:") as conn:
conn.row_factory = dict_factory
setup(conn)
##################################################
# This is the code that matters. The rest is setup noise.
conn.create_function("date_parse", 1, date_parse)
cur = conn.cursor()
cur.execute(''' select "date", date_parse("date") as parsed from _test order by 2; ''')
pprint(cur.fetchall())
##################################################
def setup(conn):
''' Setup some values to parse '''
cur = conn.cursor()
# Make a table
sql = '''
create table _test (
"id" integer primary key,
"date" text
);
'''
cur.execute(sql)
# Fill the table
dates = [
'2/1/03', '03/2/04', '4/03/05', '05/04/06',
'6/5/2007', '07/6/2008', '8/07/2009', '09/08/2010',
'2-1-03', '03-2-04', '4-03-05', '05-04-06',
'6-5-2007', '07-6-2008', '8-07-2009', '09-08-2010',
'31/12/20', '31-12-2020',
'BOMB!',
]
params = [(x,) for x in dates]
cur.executemany(''' insert into _test ("date") values(?); ''', params)
if __name__ == "__main__":
main()
This will give you these results:
[{'date': 'BOMB!', 'parsed': None},
{'date': '2/1/03', 'parsed': '2003-01-02'},
{'date': '2-1-03', 'parsed': '2003-01-02'},
{'date': '03/2/04', 'parsed': '2004-02-03'},
{'date': '03-2-04', 'parsed': '2004-02-03'},
{'date': '4/03/05', 'parsed': '2005-03-04'},
{'date': '4-03-05', 'parsed': '2005-03-04'},
{'date': '05/04/06', 'parsed': '2006-04-05'},
{'date': '05-04-06', 'parsed': '2006-04-05'},
{'date': '6/5/2007', 'parsed': '2007-05-06'},
{'date': '6-5-2007', 'parsed': '2007-05-06'},
{'date': '07/6/2008', 'parsed': '2008-06-07'},
{'date': '07-6-2008', 'parsed': '2008-06-07'},
{'date': '8/07/2009', 'parsed': '2009-07-08'},
{'date': '8-07-2009', 'parsed': '2009-07-08'},
{'date': '09/08/2010', 'parsed': '2010-08-09'},
{'date': '09-08-2010', 'parsed': '2010-08-09'},
{'date': '31/12/20', 'parsed': '2020-12-31'},
{'date': '31-12-2020', 'parsed': '2020-12-31'}]
The SQLite equivalent of anything this robust is a tangled weave of substr
and instr
calls that you should avoid.
If Source Date format isn't consistent there is some problem
with substr
function, e.g.:
1/1/2017 or 1/11/2017 or 11/11/2017 or 1/1/17 etc.
So I followed a different apporach using a temporary table. This snippet outputs 'YYYY-MM-DD' + time if exists.
Note that this version accepts Day/Month/Year format. If you want Month/Day/Year
swap the first two variables DayPart
and MonthPart
. Also, two year dates '44-'99 assumes 1944-1999 whereas '00-'43 assumes 2000-2043.
BEGIN;
CREATE TEMP TABLE [DateconvertionTable] (Id TEXT PRIMARY KEY, OriginalDate TEXT , SepA INTEGER, DayPart TEXT,Rest1 TEXT, SepB INTEGER, MonthPart TEXT, Rest2 TEXT, SepC INTEGER, YearPart TEXT, Rest3 TEXT, NewDate TEXT);
INSERT INTO [DateconvertionTable] (Id,OriginalDate) SELECT SourceIdColumn, SourceDateColumn From [SourceTable];
--day Part (If day is first)
UPDATE [DateconvertionTable] SET SepA=instr(OriginalDate ,'/');
UPDATE [DateconvertionTable] SET DayPart=substr(OriginalDate,1,SepA-1) ;
UPDATE [DateconvertionTable] SET Rest1=substr(OriginalDate,SepA+1);
--Month Part (If Month is second)
UPDATE [DateconvertionTable] SET SepB=instr(Rest1,'/');
UPDATE [DateconvertionTable] SET MonthPart=substr(Rest1, 1,SepB-1);
UPDATE [DateconvertionTable] SET Rest2=substr(Rest1,SepB+1);
--Year Part (3d)
UPDATE [DateconvertionTable] SET SepC=instr(Rest2,' ');
--Use Cases In case of time string included
UPDATE [DateconvertionTable] SET YearPart= CASE WHEN SepC=0 THEN Rest2 ELSE substr(Rest2,1,SepC-1) END;
--The Rest considered time
UPDATE [DateconvertionTable] SET Rest3= CASE WHEN SepC=0 THEN '' ELSE substr(Rest2,SepC+1) END;
-- Convert 1 digit day and month to 2 digit
UPDATE [DateconvertionTable] SET DayPart=0||DayPart WHERE CAST(DayPart AS INTEGER)<10;
UPDATE [DateconvertionTable] SET MonthPart=0||MonthPart WHERE CAST(MonthPart AS INTEGER)<10;
--If there is a need to convert 2 digit year to 4 digit year, make some assumptions...
UPDATE [DateconvertionTable] SET YearPart=19||YearPart WHERE CAST(YearPart AS INTEGER)>=44 AND CAST(YearPart AS INTEGER)<100;
UPDATE [DateconvertionTable] SET YearPart=20||YearPart WHERE CAST(YearPart AS INTEGER)<44 AND CAST(YearPart AS INTEGER)<100;
UPDATE [DateconvertionTable] SET NewDate = YearPart || '-' || MonthPart || '-' || DayPart || ' ' || Rest3;
UPDATE [SourceTable] SET SourceDateColumn=(Select NewDate FROM DateconvertionTable WHERE [DateconvertionTable].id=SourceIdColumn);
END;
This is for fecha(TEXT) format date YYYY-MM-dd HH:mm:ss for instance I want all the records of Ene-05-2014 (2014-01-05):
SELECT
fecha
FROM
Mytable
WHERE
DATE(substr(fecha ,1,4) ||substr(fecha ,6,2)||substr(fecha ,9,2))
BETWEEN
DATE(20140105)
AND
DATE(20140105);
I am storing the date as 'DD-MON-YYYY format (10-Jun-2016) and below query works for me to search records between 2 dates.
select date, substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), case
substr(date, 4,3)
when 'Jan' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Jan' , '01'))
when 'Feb' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Feb' , '02'))
when 'Mar' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Mar' , '03'))
when 'Apr' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Apr' , '04'))
when 'May' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'May' , '05'))
when 'Jun' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Jun' , '06'))
when 'Jul' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Jul' , '07'))
when 'Aug' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Aug' , '08'))
when 'Sep' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Sep' , '09'))
when 'Oct' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Oct' , '10'))
when 'Nov' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Nov' , '11'))
when 'Dec' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Dec' , '12'))
else '0' end as srcDate from payment where srcDate >= strftime('%s', '2016-07-06') and srcDate <= strftime('%s', '2016-09-06');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With