Redshift - Convert epoch string to timestamp

问题:

I have got a table with 2 columns epoch_start and epoch_end. I want to find the difference in days of these 2 epochs.
The problem i am facing is that the above columns are character varying(5000) type. The query im running is

select datediff(day,'1459762341','1450762341') as numdays;

The error i get is

ERROR: invalid input syntax for type timestamp: "1459762341"

回答1:

I have found the solution -

1. To get timestamp from epoch -

SELECT (TIMESTAMP 'epoch' + '1459762341' * INTERVAL '1 Second ') as mytimestamp

2. For datediff between two epochs -

select datediff(day,(TIMESTAMP 'epoch' + '1458762341' * INTERVAL '1 Second '), (TIMESTAMP 'epoch' + '1459762341' * INTERVAL '1 Second ')) as numdays;

回答2:

"epoc" time is just the number of seconds from 1/1/1970, so its just some counter of the number of seconds from that date.

So, epic_start can really be thought of as start_seconds, and epic_end is really just end_seconds.

The fact that they are the count of the number of seconds from the same starting point is the only thing that really matters.

To get the number of days between two numbers representing seconds from the same starting point:

days = (end_seconds - start_seconds)/60/60/24

or

SELECT (end_seconds - epic_start)/60/60/24 AS numdays

Redshift will return an integer value without the decimal portion, so if the formula returns 1.9, numdays will be 1.

回答3:

You are passing wrong parameters in datediff().

SELECT DATEDIFF('2014-11-30','2014-11-29') AS DiffDate 

Above will return the difference in days between two given dates. Read more on datediff() here datediff