Sqlite Current Timestamp with Milliseconds

SQLite Current Timestamp with Milliseconds?

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'))

For example:

CREATE TABLE IF NOT EXISTS event
(when_ts DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')));

sqlite: how to select current_timestamp as millisecond value since 1/1/1970

strftime() gives you only seconds:

SELECT strftime('%s', 'now') * 1000;

SQLite: Insert current timestamp with milliseconds precision

Date and time functions reference says datetime(timestring, [modifiers...]) is equivalent to strftime('%Y-%m-%d %H:%M:%S', timestring, [modifiers...]).

If you want fractional seconds you must use %f not %S. So use strftime('%Y-%m-%d %H:%M:%f', 'now').

SQLite add milliseconds to time

The datetime function does not format the fractional part.

You can use strftime() with the exact format you want :

(Edited to remove the redundant %S)

UPDATE sensordata
SET time=STRFTIME('%Y-%m-%d %H:%M:%f', time, '+84.000 seconds')

In fact datetime(...) is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), see Date And Time Functions for more details.

SQL Query to convert unixepoch time in nanoseconds to human readable time

You were very close with your use of STRFTIME. It will convert the value it gets to a datetime, but since you were using integer math, then it didn't get the fractional time.

SELECT 
    STRFTIME('%Y-%m-%d %H:%M:%f', CAST(timestamp AS float) / 1e9,'unixepoch') AS timestamp

This will do what you want. First convert the timestamp to a float value, then divide by 10⁹ to convert the value to a unix epoch.

Sqllite query convert timestamp to date

Your dates are unix epoch times with milliseconds.

You must divide event_date by 1000 to strip off the milliseconds and then use the function date() or datetime():

SELECT date(event_date / 1000, 'unixepoch')
FROM ...

How to convert milliseconds to date in SQLite

One of SQLite's supported date/time formats is Unix timestamps, i.e., seconds since 1970.
To convert milliseconds to that, just divide by 1000.

Then use some date/time function to get the year and the month:

SELECT strftime('%Y-%m', MillisField / 1000, 'unixepoch') FROM MyTable

SQLite difference between dates in millisecond

Since SQLite does not have a native datetime data type, instead forcing you to either store as a string or as a number, and you have picked a string, you need to convert it into something that can be calculated upon.

You can use the julianday function for this.

To calculate the difference between two values you would simply do this:

SELECT julianday('2006-07-01 12:08:15.310')-julianday('2006-07-01 12:08:14.141')

This, however, will give you the difference in days. One day contains 86400 seconds and thus 86400000 milliseconds, which gives you this:

SELECT (julianday('2006-07-01 12:08:15.310')-julianday('2006-07-01 12:08:14.141'))*86400000

Note that the precision of the floating point types used internally by SQLite does not have enough precision to get the above value accurate but it should likely be close enough to give you millisecond-precision.

For instance, this:

select (julianday('2006-07-01 12:08:14.141')-julianday('2006-07-01 12:08:14.140'))*(86400000)

Which should give 1 millisecond of difference gives this result:

1.00582838058472

---

Related Topics