Android Question SQLite: filter on milliseconds

[LucaMs]

I have an Integer field in which I save the dates expressed as ticks.

Everything is ok.

But how is it possible to filter for milliseconds?

I get properly:
==================
* Minutes > 24 *
==================
SELECT * FROM [MyTable] WHERE strftime('%M', datetime([DTField]/1000, 'unixepoch')) > '24'
22/01/2000 23:55:10:000 Code: 2
02/09/1999 11:25:50:500 Code: 3
12/10/1492 14:59:59:000 Code: 5

But:
SELECT strftime('%f', datetime([DTField], 'unixepoch')) FROM [MyTable]

returns all "12.000"

 

[Douglas Farias]

i dont know if this go help u

http://stackoverflow.com/questions/16977230/how-to-convert-milliseconds-to-date-in-sqlite

Instead of CURRENT_TIMESTAMP, use (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) so that your column definition become:

TIMESTAMP DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f','NOW'))

The following will return the number of milliseconds since the UNIX Epoch:

SELECT(strftime('%s','now')- strftime('%S','now')+ strftime('%f','now'))*1000AS ticks
It works by grabbing the number of seconds since the Unix Epoch (%s), subtracting the number of seconds in the current time (%S), adding the number of seconds with decimal places (%f), and multiplying the result by 1000 to convert from seconds to milliseconds.

The subtraction and addition are to add precision to the value without skewing the result. As stated in the SQLite Documentation, all uses of 'now' within the same step will return the same value.

 

[LucaMs]

It is a real headache

Thank you, anyway.

 

[eps]

So it's returning all 12,000 records?

Personally I would not filter in the SQL statement, or attempt to store the date as a String with the Date and Time.

OR

Retrieve all of the records and then filter or manipulate and filter.

 

[LucaMs]

Sorry, it returns 12.000 "milliseconds" (they should be milliseconds).

Until today I have stored dates as strings yyyy/mm/dd but I'm doing tests with Ticks.

Strangely, also the first query should be wrong, because the unixepoch refers only to 1970

 

[LucaMs]

no solution, but a clarification:

even ticks of B4A are referred to 01/01/1970.

They are negative for earlier dates.

 

[stevel05]

Can't you get the range you want as ticks, then treat the query as numbers. i.e. Use B4a to do the Date conversion and not SQL.

 

[LucaMs]

Can't you get the range you want as ticks, then treat the query as numbers. i.e. Use B4a to do the Date conversion and not SQL.

English and technical complication does not allow me to understand.

Given all this interests me only in theory (fortunately and for now):

you can get the parts: year, month, and day using %Y , %m and %d in queries; in the documentation for SQLite is also present %f.

Again for my English and probably also for the lack of clarity of the documentation, I do not know how but I think it is possible; otherwise, what is the purpose of %f ?



[P.S.] from the documentation, example of clarity!:

All five date and time functions take a time string as an argument (the timestring parameter).

Examples:
SELECT datetime(1092941466, 'unixepoch')

datetime is one of that five functions; to me 1092941466 don't seems a time string argument!

 

[stevel05]

What I meant was something like:

Dim TimeTicks As Long = DateTime.DateTimeParse("DD/MM/YYYY","12:00:00:500")

'Then in the SQL statement something like
SELECT whatever Where datetimeField = timeticks

 

[LucaMs]

yes, but "i need" (!) to compare date field values.

thanks Steve, it is just a sort of quiz

 

[LucaMs]

oh now!

Now ... everything gets worse!

This query:
select strftime('%Y-%m-%d %H:%M:%f', 'now')

returns:
2014-07-10 17:10:43.994 (emulator -2 hours)

DateTime.Now: 1405012038386

so, if i replace that 'now' with 1405012038386:
select strftime('%Y-%m-%d %H:%M:%f', 1405012038386)

I get a wonderful:
1698-19-20 -1:-1:-55.80

 

[LucaMs]

In addition, this query will return exact results, seconds and milliseconds:

SELECT strftime('%f','now')

 

[LucaMs]

ok, the way to get the milliseconds from a date field filled with B4A ticks is:

SELECT substr(DateField, length(DateField)-2) FROM Table