Represent Time in Widgets

As Sisense currently supports daily data, in the following article we will demonstrate how to include time in Sisense Web widgets that is present in DateTime fields.

1. In order to display time format along with date we would Represent DateTime field as Text
2. In order to filter by time format we would Represent DateTime field as BigInt

Represent DateTime As Text

1. Add a custom Text field to your table
2. To ensure that the text field is sorted in order, we will format it as 'yyyy-mm-dd HH:mm:ss'.
   • If your datetime field is in the following format: 'yyyy-mm-dd HH:mm:ss', edit the field and insert the following script replacing "[A]" by your datetime field:

     SubString(ToString([A]),0,IndexOf(ToString([A]),'.',1))

   • If your datetime field is in another format, edit the field and insert the following script replacing "[A]" by your datetime field:

     tostring(getyear([A]))
     
      + '-' + 
     
     (CASE WHEN GETMONTH([A])< 10 THEN '0' + tostring(GETMONTH([A])) ELSE tostring(GETMONTH([A])) END)
     
      + '-' + 
     
     (CASE WHEN GETDAY([A])< 10 THEN '0' + tostring(GETDAY([A])) ELSE tostring(GETDAY([A])) END)
     
      + ' ' + 
     
     (CASE WHEN GETHOUR([A])< 10 THEN '0' + tostring(GETHOUR([A])) ELSE tostring(GETHOUR([A])) END)
     
      + ':' + 
     
     (CASE WHEN GETMINUTE([A])< 10 THEN '0' + tostring(GETMINUTE([A])) ELSE tostring(GETMINUTE([A])) END)
     
      + ':' + 
     
     (CASE WHEN GETSECOND([A])< 10 THEN '0' + tostring(GETSECOND([A])) ELSE tostring(GETSECOND([A])) END)

3. Build your ElastiCube, refresh Sisense Web
4. Use this field to present the full DateTime format in a the x-axis of a line chart, rows of pivot table etc.

Represent DateTime As BigInt

1. Add a custom BigInt field to your table
2. Edit the field and insert the following script replacing "[A]" by your datetime field:
   

   GETYEAR([A])*10000000000
   +GETMONTH([A])*100000000
   +GETDAY([A])*1000000
   +GETHOUR([A])*10000
   +GETMINUTE([A])*100
   +GETSECOND([A])
   

3. Build your ElastiCube, refresh Sisense Web
4. Use this field to filter the original DateTime field ([A]) in your widget to latest date or otherwise.