unix_timestamp not working in PL SQL

[PriteshP23]

Hello,

I have one query with timestamp. I got below error.

select users.login, 
FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(ticket_history.create_time)-UNIX_TIMESTAMP(ticket.create_time))) 
from ticket_history_type
inner join ticket_history on ticket_history.history_type_id=ticket_history_type.id
inner join ticket on ticket.id = ticket_history.ticket_id
inner join users on users.id = ticket_history.change_by
where
ticket.create_time BETWEEN '2014-10-01' AND '2014-10-15'
AND
ticket_history.name ='%%new%%Fermeture réussie%%'
group by users.id

error: function unix_timestamp(timestamp without time zone) does not exist

Thanks in advanced for your input.

[pritaeas]

Where did you learn about that function? Any documentation?

[PriteshP23]

I found only this

I need to find equivalent of this SQL query in PL SQL.

[PriteshP23]

Well, i tried from this blog but i have both functions in the same query.

Not working :(

Any idea to get result in single pl sql query ?

[pritaeas]

Did you create the function? I have no Postgres to test with atm.

[PriteshP23]

This is SQL query, function is already exist

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

I need to find equivalent of this SQL query in PL SQL.

I hope i am clear.

[PriteshP23]

@pritaeas

There is a function "to_timestamp".

select users.login, to_timestamp( AVG( extract(epoch from ticket_history.create_time) - extract(epoch from ticket.create_time) ) ) from ticket_history_type
inner join ticket_history on ticket_history.history_type_id=ticket_history_type.id
inner join ticket on ticket.id = ticket_history.ticket_id
inner join users on users.id = ticket_history.change_by
where
ticket.create_time BETWEEN '2014-10-01' AND '2014-10-15'
AND
ticket_history.name ='%%new%%Fermeture réussie%%'
group by users.id

Error: I got all dates like this: "1970-01-01 06:54:49.544299+01"

Why all rows with same date "1970-01-01" ? It should take value of column.

I have date in this format in both table columns: '%Y-%m-%d %H:%M:%S'

Thanks in advanced..!

[pritaeas]

Do you have a link to the manual for to_timestamp ?

[PriteshP23]

Do you have a link to the manual for to_timestamp ?

We can find here

I did from this Ref_Link