Changing a 26 character string column to timestamp in SQL for Athena Query-CodePudding

I have the following data stream under "mycolumn".

1996-02-14-02.28.37.404000

1996-02-14-02.28.37.404001

1996-02-14-02.28.37.418000

1996-02-16-02.53.00.248000

1996-02-16-02.53.00.265000

How can I use a SQL where statement on Athena to select values between to two dates. I attempted the following but failed.

select * from "mytable"
where mycolumn > to_char('YYYY-MM-DD-HH.MIN.SS.zzzzzz', '1996-02-14-02.28.37.404000')
and mycolumn < to_char('yyyy-mm-dd-hh.mi.ss.zzzzzz', '1996-02-16- 02.53.00.265000')

SYNTAX_ERROR: line 3:20: Unexpected parameters (varchar(27), varchar(26)) for function to_char. Expected: to_char(timestamp with time zone, varchar)

select * from "mytable"
where "mycolumn"
between TIMESTAMP '1996-02-14-02.28.37.404000'
and TIMESTAMP '1996-02-16-02.53.00.265000';
SYNTAX_ERROR: line 3:9: '1996-02-14-02.28.37.404000' is not a valid timestamp literal

CodePudding user response：

Use date_parse() to convert your text timestamps into bona fide timestamps, and then use a range comparison with valid timestamp literals:

SELECT *
FROM mytable
WHERE DATE_PARSE(mycolumn, '%Y-%m-%d-%H.%i.%s.%f') > timestamp '1996-02-14 02:28:37.404' AND
      DATE_PARSE(mycolumn, '%Y-%m-%d-%H.%i.%s.%f') < timestamp '1996-02-16 02:53:00.265';

But note that it would be better to not store timestamps as plain text. If you instead use a proper timestamp column, then we don't need the ugly calls to date_parse() to write your query.