Member Avatar for 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.

  • 2 Contributors
  • 8 Replies
  • 653 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by PriteshP23
Member Avatar for PriteshP23

I found only this

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

Member Avatar for 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 ?

Edited by PriteshP23 because: code
Member Avatar for 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..!

Member Avatar for PriteshP23

Do you have a link to the manual for to_timestamp ?

We can find here

I did from this Ref_Link

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.