string Sqlite 将字符串转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4428795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sqlite convert string to date
提问by Claudio
I have date stored as string in an sqlite database like "28/11/2010". I want to convert the string to date.
我将日期作为字符串存储在像“28/11/2010”这样的sqlite数据库中。我想将字符串转换为日期。
Specifically I have to convert lots of string dates between two dates.
具体来说，我必须在两个日期之间转换大量字符串日期。
In postgresql, I use to_date('30/11/2010','dd/MM/yyyy'), how can I do the same thing with sqlite?
在 postgresql 中，我使用to_date('30/11/2010','dd/MM/yyyy')，如何用 sqlite 做同样的事情？
Something like this:
像这样的东西：
SELECT * FROM table
WHERE to_date(column,'dd/MM/yyyy')
BETWEEN to_date('01/11/2010','dd/MM/yyyy')
AND to_date('30/11/2010','dd/MM/yyyy')
回答by
As Sqlite doesn't have a date typeyou 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
由于 Sqlite没有日期类型，因此您需要进行字符串比较才能实现这一点。为此，您需要颠倒顺序 - 例如从 dd/MM/yyyy 到 yyyyMMdd，使用类似
where substr(column,7)||substr(column,4,2)||substr(column,1,2)
between '20101101' and '20101130'
回答by John BG
Saved date as TEXT( 20/10/2013 03:26 ) To do query and to select records between dates?
Saved date as TEXT( 20/10/2013 03:26 ) 做查询和选择日期之间的记录？
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.
2013 年 10 月 20 日的 substr 给出了 20131020 日期格式 DATE(20131021) - 这使得 SQL 处理日期并使用日期和时间函数。
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');
回答by MPelletier
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).
您应该研究的一件事是SQLite 日期和时间函数，尤其是在您将不得不操作大量日期的情况下。这是使用日期的明智方法，代价是更改内部格式（必须是 ISO，即 yyyy-MM-dd）。
回答by mattmc3
The UDF approach is my preference compared to brittle substrvalues.
与脆弱substr值相比，UDF 方法是我的偏好。
#!/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 substrand instrcalls that you should avoid.
SQLite 等价于任何这种健壮的东西是你应该避免的混乱的编织substr和instr调用。
回答by anefeletos
If Source Date format isn't consistent there is some problem
with substrfunction, e.g.:
如果源日期格式不一致，则substr函数存在一些问题，例如：
1/1/2017or1/11/2017or11/11/2017or1/1/17etc.
1/1/2017或1/11/2017或11/11/2017或1/1/17等。
So I followed a different apporach using a temporary table. This snippet outputs 'YYYY-MM-DD' + time if exists.
所以我使用临时表遵循了不同的方法。如果存在，此代码段输出 'YYYY-MM-DD' + 时间。
Note that this version accepts Day/Month/Year format. If you want Month/Day/Year
swap the first two variables DayPartand MonthPart. Also, two year dates '44-'99 assumes 1944-1999 whereas '00-'43 assumes 2000-2043.
请注意，此版本接受日/月/年格式。如果您希望月/日/年交换前两个变量DayPart和MonthPart. 此外，两年日期 '44-'99 假定为 1944-1999，而 '00-'43 假定为 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;
回答by Karthi G
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.
我将日期存储为 'DD-MON-YYYY 格式（10-Jun-2016），下面的查询适用于我搜索 2 个日期之间的记录。
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');
回答by Jesus
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):
这是 fecha(TEXT) 格式日期 YYYY-MM-dd HH:mm:ss 例如我想要 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);
回答by Tschallacka
I have a database where dates are stored in d F Yformat (20 Nov 2017) and to convert it to a machine readable date(Y-m-d) I use this method to update the entire table to a proper format.
我有一个数据库，其中日期以d F Y格式存储（2017 年 11 月 20 日）并将其转换为机器可读的日期（Ymd），我使用此方法将整个表更新为正确的格式。
If you only want the date formatting look at the inner select how I formatted the date.
如果您只想查看内部日期格式，请选择我如何设置日期格式。
update TABLENAME as realTABLE set created_at = (
select
-- Get Year
substr(tmpTABLE.created_at ,-4, 4)
|| '-' ||
-- Get Month
substr(
replace (replace (replace (replace (replace (replace (replace (replace (replace (replace (replace (replace (tmpReis.szAanmaakDatum
, ' Dec ', '-12-') , ' Nov ', '-11-') , ' Oct ', '-10-') , ' Sep ', '-09-') , ' Aug ', '-08-') , ' Jul ', '-07-') , ' Jun ', '-06-') , ' May ', '-05-') , ' Apr ', '-04-') , ' Mar ', '-03-') , ' Feb ', '-02-') , ' Jan ', '-01-')
-- Get it from the original space location + 1, then get the two numbers.
,instr(tmpTABLE.created_at, ' ')+1, 2)
|| '-' ||
-- Get day, prepend with a zero if there's a zero lacking.
substr('00' || tmpTABLE.created_at, -2, 2) as foo
from TABLENAME as tmpTABLE
where tmpTABLE.id = realTABLE.id
-- Check for valid matching formats. don't do those that already were converted.
) where created_at like '_ ___ ____'
or created_at like '__ ___ ____';
