What is the sql statment needed to translate a date field to a readable date?

Question

I am writing the sql for a custom report that displays dates.  In the database the date field is a bigint field with that looks like "1516391122438", is there a way to convert this to a normal date field?  (it's the complete_date in the table AO_56464C_APPROVAL).  I've tried a couple of standard sql tricks dateadd but can't come back with the date.

Payne · Accepted Answer

Nic is correct, but to give you a bit more to go on ... I'm going to guess that you're using MySql, in which case this may be of some help to you - https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime

unknown · Accepted Answer

Thanks for the tips, SQL is a poor choice agreed, but reporting in Jira Service Desk is weak without purchasing additional tools.

The reference provided did not work, but with my buddy google, I found this and it seems to work.

DATEADD ( SECOND,   <bigint jira date>  / 1000, '19691231 19:00') as "MyReadableDate"