CODEWITHSUNDEEP
sqlsqlitedatetime
Carrol
Carrol

Reputation: 1285

SQLite - TZ format to Date time only using SQL queries

I have a SQLite database with a simple table in the format of:

ID,DateText,Name
1,2020-09-01T18:57:17Z,John
2,2022-12-01T04:00:09Z,Laurel
...

The DateText column is declared as TEXT on the "create table" statement. Using only SQL, I need to:

  1. Create a new column with the DateText data.
  2. Obtain the "oldest" date
  3. Obtain the "newest" date

Note that I need to resolve this with a SQL query. I cannot read into a programming language, parse, and update table--I need to do everything on SQL. For example, SQL Server DateTime with timezone is the opposite, but they are using node.js, something I cannot do.

Upvotes: 0

Views: 166

Answers (1)

Shawn
Shawn

Reputation: 52344

You can get the oldest and newest using min() and max():

SELECT ID, min(DateTime), Name FROM YourTable; -- Oldest
SELECT ID, max(DateTime), Name FROM YourTable; -- Newest

The nice thing about ISO-8601 date and time format strings is that they sort lexicographically without having to do anything special with them.

These queries would give an error on most SQL database engines because of the mix of non-grouped columns with an aggregate function, but SQLite explicitly will return the row that goes with the minimum or maximum column value. And of course if you don't want the other columns, just leave them out of the SELECT.

Upvotes: 1

Related Questions