And do not forget to always convert your datetime to UTC before storing, especially if you live somewhere where the timezone offset from UTC has ever changed (which includes the entire planet earth). Wallclock timestamps in any localtime format cannot be compared unless your platform time functions are timezone aware -- in which you have to store any datetime value that is in string format with a UTC offset and then "convert" the datetime to utc datetime or utc epoch offset before comparing it with anything. Strings with embeded UTC offsets are cannot be compared unless all the offsets are the same.)
> -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of David Raymond > Sent: Friday, 21 October, 2016 13:43 > To: SQLite mailing list > Subject: Re: [sqlite] Troubles matching variable as type TIMESTAMP > > "Python 3.19"? > > SQLite doesn't have a set datetime record format. It's up to you to > standardize the input. There're some built-in functions to help out, but > you have to format it yourself. If you're doing them as standardized > strings, ('2016-10-21 15:40:14') then when you're retrieving them from > the database you can run the resulting string through strptime to get a > Python datetime. And when you're inserting or comparing from a Python > datetime, you should use strftime on the Python datetime to turn it into > the appropriate string before passing it to SQLite, as it will do a > textual comparison against the other text entries in there. > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Rick Kohrs > Sent: Friday, October 21, 2016 3:05 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Troubles matching variable as type TIMESTAMP > > Using Python 3.19. > I am reading in a lines from an ever growing log file. Values from each > line of the log file are parsed and placed into a database. Each record > has a variable of type TIMESTAMP. Multiple records have the same the > same value for time stamp. > After a line is processed, I need to check if there are multiple records > with the same time stamp and count the total number of records returned. > I'm struggling trying to create a select clause to match a variable of > type TIMESTAMP. > > Example Record > (datetime.datetime(2016, 10, 13, 8, 10), > u'HS_H08_20161013_0810_B09_JP03_R20_S0101.DAT', u'Himawari8', 2016, 10, > 13, 8, 10, 9, u'Japan', -1, 3, u'test') > > Code snipits: > > #HS_H08_20161013_0000_B01_R304_R10_S0101.DAT > > imageInfo['year'] = int(filenameVals[2][0:4]) > imageInfo['month'] = int(filenameVals[2][4:6]) > imageInfo['day'] = int(filenameVals[2][6:8]) > > imageInfo['hour'] = int(filenameVals[3][0:2]) > imageInfo['minute'] = int(filenameVals[3][2:4]) > imageInfo['band'] = int(filenameVals[4][2:4]) > > string_date = (str(imageInfo['year']) + '-' + > str(imageInfo['month']) + '-' + > str(imageInfo['day']) + ' ' + > str(imageInfo['hour']) + ':' + > str(imageInfo['minute']) + ':00.0') > imageInfo['dateTime'] = datetime.datetime.strptime(string_date, > "%Y-%m-%d %H:%M:%S.%f") > > ....... > sqlCommand = """ > CREATE TABLE himawari_db ( > date_time TIMESTAMP, > ........ > checkTime = imageInfo['dateTime'] > > print(checkTime) > try: > satDB.execute("SELECT * FROM himawari_db WHERE date_time=? > ",(checkTime,)) > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
