How can I convert string to datetime

(1) By Jorge Luiz Plautz (jlplautz) on 2025-04-10 20:26:06 [link] [source]

Dear Sqlite User Team.

I am trying to execute a query by grafana into SQLITE datasource,
but it is not working. 

I believe the column (createAt) has the information, but it is string type.
I am asking some help to understand how can I convert the column with string information to datetime (timestamp).

I saw the information inside Grafana, but it was not clear enough to mu understanding.

******************************************************************************
Grafana advice...

Timestamps stored as unix epoch should work out of the box, but the string formatting might require adjusting your current format. The below example shows how to convert a "date" column to a parsable timestamp:

WITH converted AS (
   -- a row looks like this (value, date): 1.45, '2020-12-12'
   SELECT value,  date || 'T00:00:00Z' AS datetime FROM raw_table
)
SELECT datetime, value FROM converted ORDER BY datetime ASC

Have you any tips to help me.

Regards

Plautz


example of my datasource

id      siteName      Site Element     reportId GreatAt       empty    kpiValue
6207	BANDEIRANTE	MRBTS-621901	16185	2025-04-08 07:15	0	9.48958333333333
6208	CMAA - SANTA VITORIA	MRBTS-342504	16185	2025-04-08 07:15	0	45.70833333333334
6209	CMAA CNP2 SÃO PEDRO	MRBTS-342501	16185	2025-04-08 07:15	0	13.79166666666667
6210	BIOENERGÉTICA AROEIRA	MRBTS-342401	16185	2025-04-08 07:15	0	0.20833333333333
6211	CORAMANDEL	MRBTS-341601	16185	2025-04-08 07:15	0	11.86458333333333
6212	AMAGGI T4 SEDE	MRBTS-100004	16185	2025-04-08 07:15	0	6.66666666666667
6213	AMAGGI T2 IVIPORÃ	MRBTS-100001	16185	2025-04-08 07:15	0	1.95833333333333
6188	RIZA	MRBTS-772601	16184	2025-04-08 07:00	0	5.96875
6189	CACHOEIRA DO CAFE	MRBTS-771801	16184	2025-04-08 07:00	0	1.59375
6190	JABORANDI - FAZ. SANTA EFIGÊNIA	MRBTS-771701	16184	2025-04-08 07:00	0	55.08333333333334
6191	AMAGGI T3 IVIPITA ST02	MRBTS-690513	16184	2025-04-08 07:00	0	0.39583333333333


******************************************************************************

(2) By Mike Swanson (chungy) on 2025-04-10 23:28:55 in reply to 1 [link] [source]

SQLite has no date nor datetime data type, though specifying DATE or DATETIME for a column in a table will have an affinity for the NUMERIC type. How you choose to store dates in SQLite is entirely up to you, good options are usually as just strings (the TEXT type) representing an ISO 8601 format string, a Unix timestamp (the NUMERIC type) representing seconds since 1970, and a Julian day (also NUMERIC type) representing days since -4714-11-24.

SQLite has functions to deal with all three of these formats, and what you use is entirely your preference and what is convenient in your application. You could even choose another option, but I would strongly suggest to pick to one of these three, especially given the built-in functions handling dates of various formats.

Since you have a table that looks like CREATE TABLE t ( GreatAt TEXT );, already using ISO 8601 strings, that seems to be the best option for you. You can use queries to convert to the other two, eg: SELECT unixepoch(GreatAt), julianday(GreatAt) FROM t;.

(3) By Holger J (holgerj) on 2025-04-11 12:26:55 in reply to 1 [link] [source]

If you want to have a column checking that what you enter is actually a correct date, you might try the following:

create table d (
  day   date not null check (strftime('%F', unixepoch(day), 'unixepoch') = day),
  n     integer primary key  
);

Every value for day is checked whether the conversion to a unix epoch and back to iso8601 delivers the same value. Some examples:

insert into d values ('2025-02-28', 1);
insert into d values ('2025-02-29', 2);
Runtime error: CHECK constraint failed: strftime('%F', unixepoch(day), 'unixepoch') = day (19)
insert into d values ('2024-02-29', 2);

 select * from d;
┌────────────┬────────┐
│    day     │ number │
├────────────┼────────┤
│ 2025-02-28 │ 1      │
│ 2024-02-29 │ 2      │
└────────────┴────────┘

(4) By Jorge Luiz Plautz (jlplautz) on 2025-04-13 18:06:54 in reply to 2 [link] [source]

Thanks Mike.

I changed it to PostgreSQL, but I will check your suggestion.

REgards

(5) By Jorge Luiz Plautz (jlplautz) on 2025-04-13 18:08:12 in reply to 3 [source]

Thanks Holge.

I will check it.

Regards

Jorge Plautz