Timestamps are stored in the databases as one of several numerical representations. Most applications will use UNIXEPOCH time, where the time is representative of the number of seconds since 01/01/1970 00:00:00. This is usually easy to spot, since it is a 10-digit number. This ten-digit number is converted to supply the resulting time in UTC:
SELECT datetime(time COLUMN, 'unixepoch');

or in local time as suggested by the device settings: NOTE: This will be reflective of your forensic workstation time zone settings!
SELECT datetime(time COLUMN, 'unixepoch', 'localtime');

UNIXEPOCH is also calculated to the millisecond in some applications for an even more accurate representation of the time. Also, easy to spot, this is a 13-digit number that must first be divided by 1,000 before making the conversion.¹

MILLISECONDS (13-digit number)
Convert milliseconds in your query, like this
SELECT datetime(time COLUMN/1000,'unixepoch','localtime');

Another commonly used time format is Mac Absolute time, which is defined as the number of seconds since 01/01/2001 00:00:00. Like the name suggests, this timestamp is utilized by most iOS applications. In order to correctly convert this timestamp, first add the number of seconds since UNIXEPOCH time to Mac Absolute time, 978307200, then convert to local time, as in the queries above.²
SELECT datetime(time COLUMN + 978307200, 'unixepoch', 'localtime');

These are not the only timestamps that you may come across when analyzing application data, but they are the timestamps that appear most frequently. Some timestamps, like Chrome as an example, account for time accurate to the MICROSECOND, which requires dividing the number by 1,000,000:³
SELECT datetime(time COLUMN/1000000 + (strftime('%s','1601-01-01')),’unixepoch','localtime');

References:

[1] https://for585.com/unixepochtimestamps

[2] https://for585.com/macabsolutetimestamps

[3] https://for585.com/chrometimestamps