Retrieve only records with today's date from an epoc timestamped column

Question

I have a table which has 'completed' column with the timestamp but the timestamp is in epoch format. I am using the below query to translate the epoch time to a normal date but I am not sure how to filter the records that were created today. Hope someone can assist.

Here is the query I am using:

select name, status, message, completion_status, 
TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * completed AS TIMESTAMP), 'America/New_York'), 'yyyy-mm-dd HH24:MI:SS') 
as completed from Result;

[epoch](https://stackoverflow.com/a/52955995/10457877) – Georgy Feb 05 '19 at 18:31 — Georgy, Feb 05 '19 at 18:31

score 0 · Answer 1 · answered Feb 05 '19 at 18:54

Found it:

select name, status, message, completion_status, 
TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * completed AS TIMESTAMP), 'America/New_York'), 'yyyy-mm-dd HH24:MI:SS') 
as completed from Result
where TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * completed AS TIMESTAMP), 'America/New_York'), 'yyyy-mm-dd') 
LIKE (SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') as today_date FROM dual);

Retrieve only records with today's date from an epoc timestamped column

1 Answers1