Sqlite Timestamp Formatting

sqlite timestamp formatting

You need to convert the timestamp to datetime first:

SELECT strftime('%d - %m  - %Y ', datetime(1281353727, 'unixepoch')) FROM Visits;

Timestamp formatting in sqlite

Your Unix timestamps contain milliseconds, so use this:

select 
  strftime(
    '%d - %m  - %Y', 
    max(datetime(ERDAT / 1000, 'unixepoch', 'localtime'))
  ) as maxdate
from tablename

The format %d - %m - %Y that you want is not comparable. So you must first get the max date and then apply this format.

See the demo.

Results:

| maxdate         |
| --------------- |
| 02 - 01  - 2019 |

SQLite - TimeStamp to Date

To expand on @forpas comment, SQLite does not have a TIMESTAMP data type, so when you insert values into your fromDateTime and toDateTime column they are converted to one of SQLite's 5 data storage classes: NULL, INTEGER, REAL, TEXT, BLOB. Since there is no error on INSERT, this gives the impression that SQLite has recognised the value as a timestamp, when in fact the value has just been treated as TEXT. Now to use those values in any of SQLite's Date and Time functions they must either be an ISO-8601 compatible string, the word now, or a number (interpreted as either a Julian day number or a Unix timestamp dependent on the context). So, you need to change your times to YYYY-MM-DD hh:mm:ss format i.e.

Insert INTO Appointments ( fromDateTime, toDateTime, name, description ) VALUES
('2020-03-21 15:00:00', '2020-03-21 16:00:00', 'Test', 'Test Description'), 
('2020-03-22 15:00:00', '2020-03-22 16:00:00', 'Test 2', 'Test 2 Description'),    
('2020-03-22 16:00:00', '2020-03-22 17:00:00', 'Test 2', 'Test 2 Description'),    
('2020-03-22 17:00:00', '2020-03-22 18:00:00', 'Test 2', 'Test 2 Description'),           
('2020-03-21 00:00:00', '2020-03-25 23:59:59', 'Test', 'Test Description'),      
('2020-03-27 08:00:00', '2020-03-21 12:00:00', 'Test', 'Test Description'),
('2020-03-02 08:00:00', '2020-03-10 12:00:00', 'Joelle', 'Test Joelle');

Note that datetime is simply called with the column as a parameter and returns the string in an ISO-8601 format. To get YYYY-MM-DD format you need to use strftime as well. So your query becomes:

SELECT strftime('%d - %m - %Y', fromDateTime) AS y,
       strftime('%Y-%m-%d', fromDateTime) AS x
FROM Appointments

And the output:

y               x
21 - 03 - 2020  2020-03-21
22 - 03 - 2020  2020-03-22
22 - 03 - 2020  2020-03-22
22 - 03 - 2020  2020-03-22
21 - 03 - 2020  2020-03-21
27 - 03 - 2020  2020-03-27
02 - 03 - 2020  2020-03-02

Demo on dbfiddle

How to convert timestamp to string in SQLite?

Try this:

SELECT date raw, strftime('%d-%m-%Y', datetime(date/1000, 'unixepoch')) as_string
  FROM my_table

You need to convert timestamp to date before.

How can I convert timestamps to dates in SQLite?

The strftime is meant to format a date, rather than perform conversion.

In the meantime you could try something like that to gather the pieces:

SELECT Timestamp, 
SUBSTR(c,7,4) || '-' || SUBSTR(Timestamp,1,2) || '-' || SUBSTR(Timestamp,4,2) as Date
FROM Room_Data

Since SQlite doesn't really have the concept of a date, unlike other DBMS, the best choice would be to convert your dates to integer, either as Unix timestamps or in string form (YYYY-MM-DD) but storing dates as integer like 20201010 would be acceptable too.

NB: be careful with names like Timestamp or Date, they are reserved keywords in many programming languages and DBMSes.

What date format is 623548800?

Your dates are Unix Timestamps.

By using any on line converter (like https://www.epochconverter.com) you can find the dates they correspond to.

The latest value 623548800 corresponds to Thursday, October 5, 1989 12:00:00 AM GMT

and the earliest value 603244800 corresponds to Sunday, February 12, 1989 12:00:00 AM GMT.

So it seems like your dates or off by 31 years.

I found a similar case here: Behind The Scenes: Core Data dates stored with 31 year offset?

If you want you can convert them to the format 'YYYY-MM-DD hh:mm:ss' like this:

UPDATE tablename
SET datecolumn = datetime(datecolumn, 'unixepoch', '+31 year')

or:

UPDATE tablename
SET datecolumn = date(datecolumn, 'unixepoch', '+31 year')

if you are not interested in the time part.

How to change date format in sqlite database

try this query

select strftime('%d/%m/%Y',datetime(substr(t_date, 7, 4) || '-' || substr(t_date, 4, 2) || '-' || substr(t_date, 1, 2))) from party_trans;

Save SQLite TIMESTAMP in the format of MySQL TIMESTAMP, Android

Have a look at SQLite date and time functions. You would do something like
strftime(%Y-%m-%d %H:%M:%S, 'now') in your SQL string. This particular format also has a shortcut: datetime('now')

db.execSql("insert into table (column) values ( datetime('now') )");

My preference though is to simply store the current time as a long, which you can then format any way you like (or any way the user likes, or any way the system defaults to showing dates/times).

---

Related Topics