Converting 13-Digit Timestamps to Readable Dates in SQLite

Timestamps in Milliseconds: Why datetime() Returns Blank

When working with timestamps in SQLite, a common task is converting them into a human-readable format using the datetime() function. However, users often encounter issues where the datetime() function returns a blank or null value. This typically occurs when the timestamp format does not match the expected input for the datetime() function. In the provided scenario, the date column in the smstable table contains a 13-digit timestamp, which represents milliseconds since the Unix epoch (January 1, 1970). The datetime() function in SQLite, however, expects a timestamp in seconds, not milliseconds. This discrepancy is the root cause of the blank output.

The datetime() function in SQLite is designed to work with Unix timestamps, which are typically 10 digits long and represent the number of seconds since the Unix epoch. When a 13-digit timestamp (milliseconds) is passed directly to the datetime() function, it falls outside the valid range for the function, resulting in a blank or null value. This is a common oversight, especially when dealing with timestamps generated by systems that use millisecond precision, such as Java or JavaScript.

To further complicate matters, SQLite does not have a native LONG data type, as mentioned in the discussion. The date column in the smstable table is declared as LONG, but SQLite interprets this as having NUMERIC affinity. This means that the column can store values using any of SQLite’s storage classes (INTEGER, REAL, TEXT, BLOB, or NULL). However, the storage class does not affect the underlying issue with the timestamp format. The key takeaway here is that the datetime() function requires the timestamp to be in seconds, not milliseconds, and the 13-digit timestamp must be converted accordingly.

Misalignment Between Timestamp Precision and SQLite datetime() Function

The core issue stems from the misalignment between the precision of the timestamp stored in the date column and the precision expected by the datetime() function. The date column contains timestamps with millisecond precision, which is common in many programming languages and systems. For example, Java’s System.currentTimeMillis() and JavaScript’s Date.now() both return timestamps in milliseconds. However, SQLite’s datetime() function is designed to work with second-precision timestamps.

When a 13-digit timestamp is passed directly to the datetime() function, SQLite interprets it as a number of seconds since the Unix epoch. However, since the number is actually in milliseconds, it represents a date far in the future—beyond the range that SQLite can handle. This results in the function returning a blank or null value. For example, the timestamp 1539260029211 (which corresponds to October 11, 2018, at 12:13:49 UTC) is interpreted by SQLite as a date in the year 49,000 AD, which is clearly outside the valid range for the datetime() function.

Another factor contributing to this issue is the lack of awareness about the storage classes and type affinities in SQLite. As mentioned earlier, SQLite does not have a LONG data type, and columns declared as LONG are assigned NUMERIC affinity. This means that the values stored in the date column are treated as numbers, but the precision of these numbers (milliseconds vs. seconds) is not automatically adjusted by SQLite. It is up to the user to ensure that the timestamps are in the correct format before passing them to the datetime() function.

Additionally, the discussion highlights a potential performance consideration related to the primary key declaration in the smstable table. While this is not directly related to the timestamp issue, it is worth noting that the _id column is declared as INTEGER with a primary key constraint. However, this does not automatically make it an alias for the internal rowid, which can impact the performance of foreign key relationships. This is a separate issue but underscores the importance of understanding SQLite’s type system and storage mechanisms when designing schemas and writing queries.

Converting Milliseconds to Seconds and Using datetime() Correctly

To resolve the issue of converting 13-digit timestamps to readable dates, the key step is to convert the millisecond-precision timestamps to second-precision timestamps before passing them to the datetime() function. This can be achieved by dividing the timestamp by 1000, effectively truncating the milliseconds and leaving only the seconds. For example, the timestamp 1539260029211 can be converted to seconds by dividing it by 1000, resulting in 1539260029. This value can then be passed to the datetime() function to obtain the correct date and time.

The corrected query would look like this:

SELECT _id, thread_id, address, person, datetime(date / 1000, 'unixepoch') AS readable_date
FROM smstable;

In this query, the date column is divided by 1000 to convert the timestamp from milliseconds to seconds. The result is then passed to the datetime() function along with the 'unixepoch' modifier, which tells SQLite to interpret the value as a Unix timestamp. The output of this query will be a human-readable date and time in the format YYYY-MM-DD HH:MM:SS.

It is also important to note that the datetime() function can be customized to output dates in different formats using the strftime() function. For example, if you want to display the date in a different format, such as DD/MM/YYYY HH:MM:SS, you can use the following query:

SELECT _id, thread_id, address, person, strftime('%d/%m/%Y %H:%M:%S', date / 1000, 'unixepoch') AS readable_date
FROM smstable;

This query uses the strftime() function to format the date according to the specified pattern. The %d, %m, %Y, %H, %M, and %S placeholders represent the day, month, year, hour, minute, and second, respectively. This allows for greater flexibility in how the date is displayed.

In addition to converting timestamps, it is also important to consider the performance implications of the schema design. As mentioned earlier, the _id column in the smstable table is declared as INTEGER with a primary key constraint, but this does not automatically make it an alias for the internal rowid. To ensure optimal performance, especially when dealing with foreign key relationships, it is recommended to either declare the _id column as INTEGER PRIMARY KEY (which makes it an alias for the rowid) or use the WITHOUT ROWID clause in the table declaration. For example:

CREATE TABLE IF NOT EXISTS "smstable" (
    "_id" INTEGER PRIMARY KEY,
    "thread_id" INTEGER,
    "address" TEXT,
    "person" NUMERIC,
    "date" NUMERIC,
    "protocol" INTEGER,
    "read" INTEGER,
    "status" INTEGER,
    "type" INTEGER,
    "reply_path_present" INTEGER,
    "subject" TEXT,
    "body" TEXT,
    "service_center" TEXT,
    "locked" INTEGER,
    "error_code" INTEGER,
    "seen" INTEGER
);

This declaration ensures that the _id column is an alias for the rowid, which can improve the performance of queries and foreign key relationships. Alternatively, if you want to use a composite primary key or avoid the rowid altogether, you can use the WITHOUT ROWID clause:

CREATE TABLE IF NOT EXISTS "smstable" (
    "_id" INTEGER,
    "thread_id" INTEGER,
    "address" TEXT,
    "person" NUMERIC,
    "date" NUMERIC,
    "protocol" INTEGER,
    "read" INTEGER,
    "status" INTEGER,
    "type" INTEGER,
    "reply_path_present" INTEGER,
    "subject" TEXT,
    "body" TEXT,
    "service_center" TEXT,
    "locked" INTEGER,
    "error_code" INTEGER,
    "seen" INTEGER,
    PRIMARY KEY("_id")
) WITHOUT ROWID;

This approach makes the _id column the actual primary key of the table, without relying on the internal rowid. Both approaches have their advantages and should be chosen based on the specific requirements of the application.

In conclusion, the issue of converting 13-digit timestamps to readable dates in SQLite can be resolved by dividing the timestamp by 1000 to convert it from milliseconds to seconds before passing it to the datetime() function. Additionally, it is important to consider the schema design and ensure that primary keys are declared in a way that optimizes performance. By following these steps, you can effectively work with millisecond-precision timestamps in SQLite and avoid common pitfalls related to date and time manipulation.