SAP Knowledge Base Article - Public

2885914 - How to convert Unix Timestamp to a local date time in Crystal Reports?

Symptom

  • How to display in a date time format, a Unix Timestamp?
  • How to convert a Unix Timestamp to a local date time in Crystal Reports?

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
                
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2
  • SAP Crystal Reports for Enterprise 4.3 

Resolution

  • A Unix timestamp represent the number of seconds from January 1, 1970 at midnight in the Coordinated Universal Time (UTC), and it is not adjusted for daylight saving time.  
             
  • To convert a Unix timestamp to a date time in Crystal Reports, create a formula that will:
    • Convert the number of seconds into days;
    • Add the number of days to January 1, 1970, at midnight;
    • Adjust the date and time to the time zone where the computer is located using the function: ShiftDateTime
                  
  • The formula to convert a Unix timestamp to a local date time in Crystal Reports:
            
         Local NumberVar UnixTimeStamp := <Insert the Database Field containing the Unix Timestamp here>;
         Local DateTimeVar myDateTime;
         myDateTime := DateTime(1970,01,01,0,0,0) + ( UnixTimeStamp / 86400 );
         ShiftDateTime(myDateTime, "GMT,0,GMT", DataTimeZone)   
                         
  • The above formula convert the date time to the time zone of the local computer using the function ShiftDateTime, and DataTimeZone. You can modify the time zone used in the function manually. For information on how to use the function ShiftDateTime, see the SAP Knowledge Base Article: 1698610

Keywords

CR, UNIX Epoch time, UNIX time, POSIX time , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020