sorting - PostgreSQL: Is it possible to sort the part of the string has datetime

Question

I have the name of the column that contains the data sequence and datetime type as shown below:

DATA01_0003_20210126135705.zip
DATA01_0002_20210127135030.zip
DATA01_0004_20210126142913.zip

I want ORDER BY according to datetime, then the sequence string, is it possible?

DATA01_0002_20210127135030.zip
DATA01_0004_20210126142913.zip
DATA01_0003_20210126135705.zip

I tried the statement like below but it sort in sequence before datetime:
SELECT filename FROM tblData ORDER BY filename

DATA01_0002_20210127135030.zip
DATA01_0003_20210126135705.zip
DATA01_0004_20210126142913.zip

SELECT filename FROM tblData ORDER BY filename DESC

DATA01_0004_20210126142913.zip
DATA01_0003_20210126135705.zip
DATA01_0002_20210127135030.zip

question from:https://stackoverflow.com/questions/65913595/postgresql-is-it-possible-to-sort-the-part-of-the-string-has-datetime

Answer 1

in postgresql you can sort by expression.

to sort by the file's timestamp, it is necessary to extract that from the string. based on your examples, i am going to guess that the text after the last _ will have the date. if that assumption holds, then the following will sort by date

SELECT filename from tbldata
order by reverse(split_part(reverse(filename), '_', 1))

if the number of _ is fixed, then the trick with two reverses is unnecessary. you can instead order by split_part(filename, '_', 3)