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

[Deleted user]

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.

[Deleted user · Accepted solution]

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"

[Deleted user]

FYI, I'm using MS Sql Server

[Payne · Accepted solution]

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

[Nic Brough -Adaptavist-]

Yet another reason why reporting on Jira with SQL is a poor thing to do.

The answer here depends on your database flavour though, not Jira.  There should be some function in your database that outputs dates as a human date time.