Keith wrote:
> Correct.
> You have stored integer Unix Epoch timestamps. You cannot do "string"
> searches on integers (at least not ones like what you have asked for,
> which involves conversion of an integer representing a Unix Epoch offset
> to an ISO-8601 string, not to an ordinary "string representation of
> the integer".
>
> There is no "DATE" type in SQLite3 -- only INTEGER, REAL, TEXT, and
> BLOB -- and no magical conversion of integers into ISO-8601 strings
> or v/v.
> ~
> ~
> If you want your date_type integer to be converted to a date string,
> you need to use the function for converting integer unix epoch offsets
> into ISO-8601 date strings:
>
> SELECT id, date_type FROM date_types WHERE date(date_type, 'unixepoch')
> LIKE '2018-%-%';
>
> The usual caveats apply for knowing what your timezone is and handling
> such conversions appropriately for your platform (OS).
WHERE date(date_type, 'unixepoch') seems the way to go and will now focus
on that as a solution.
danap.
David Raymond wrote:
> You have to make everything the same type, either numeric or text.
> ~
> ~
R Smith:
> Why not add to the table both the converted Integer date[i.e:
> strftime('%s', '2017-01-01')] AND the ISO8601 date [i.e: '2017-01-01
> 00:00:00']?
>
> That way you can reference either, use any of them for calculations, and
> filter/lookup by whichever one suits the occasion best.
> ~
> ~
Thanks for the repies all. David and R. that would be nice, but the context
here is with a GUI for users so I do not get to define db schema.
I do actually have a test data types table that is loaded with Date data
with both Integer and Text content. Along with also Time, Datetime,
and Timestamp.
Yes I know all those could be Real or a Blob, but I'm trying to pick the
most likely content that users would store for those types. Integer and
Text seem the most appropriate.
danap.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
