Android Question SQLite: filter on milliseconds

LucaMs · Jul 9, 2014

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 · Jul 9, 2014

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 · Jul 9, 2014

It is a real headache

Thank you, anyway.

eps · Jul 9, 2014

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 · Jul 9, 2014

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 · Jul 10, 2014

no solution, but a clarification:

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

They are negative for earlier dates.

stevel05 · Jul 10, 2014

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 · Jul 10, 2014

stevel05 said:
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 · Jul 10, 2014

What I meant was something like:

B4X:

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 · Jul 10, 2014

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

thanks Steve, it is just a sort of quiz

LucaMs · Jul 10, 2014

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 · Jul 10, 2014

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

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

LucaMs · Jul 10, 2014

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

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

Android Question SQLite: filter on milliseconds

LucaMs

Expert

Douglas Farias

Expert

LucaMs

Expert

eps

Expert

LucaMs

Expert

LucaMs

Expert

stevel05

Expert

LucaMs

Expert

stevel05

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

Similar Threads

Android Question SQLite: filter on milliseconds

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency