Subject: Re: Date Search

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
dmp
Reply | Threaded
Open this post in threaded view
|

Subject: Re: Date Search

dmp
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Subject: Re: Date Search

Keith Medcalf

This will however only work in all GMT/UT1/UTC.  

If the "input" (ie, the string) is "localtime" then the localtime modifier needs to be added to the date() function as in:

date(date_type, 'unixepoch', 'localtime') like '2018-%'

Note that you cannot create an index on the expression date(date_types, 'unixepoch', 'localtime') so you might be better off (depending on the size of the  date_types table) converting and using numeric comparisons since the date_type column itself can be indexed).

select *
  from date_types
 where date_type >= strftime('%s', '2018-01-01', 'localtime')
   and date_type <  strftime('%s', '2019-01-01', 'localtime');


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of dmp
>Sent: Friday, 6 July, 2018 10:52
>To: [hidden email]
>Subject: [sqlite] Subject: Re: Date Search
>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users