Timezone conversion in CDS

Time zone conversion is often involved in development. How do we do date conversion in CAP?

In CAP we can quickly extend the time zone transformation column based on HANA SQL.

For Example:

We have a entity message_log:

entity message_logs: cuid{
	sending_time : DateTime;
	supplier_id: String(30);
	supplier_name : String(40);
	pur_org : String(40);
	sending_status: String(10);

}

We need to get the data from the Message Log, so we create a CDS service “MessageService”

service MessageService {
  view Logs as select from model.message_logs{
     sending_time, supplier_id, supplier_name, pur_org, sending_status
  }
}

If we want to transform the time zone of the field “sending_time”,we can use function  “LOCALTOUTC(t,timezone)” .

There have 2 parameters, t  is the time, timezone is out targe timezone code.

example: we want to change the timezone to EST.

service MessageService {
  view Logs as select from model.message_logs{
     LOCALTOUTC(sending_time,'EST') as sending_time, supplier_id, supplier_name, pur_org, sending_status
  }
}

And if we need to change the time zone to Asia/Tokyo, we can’t set the timezone to Asia/Tokyo, it will throw an exception.

We can use function ADD_SECONDS, this function can add second on the current time.

We need to change the time zone to  UTC  first, and add second. Tokyo is UTC+9 ，so need add 60*60*9 seconds.

service MessageService {
  view Logs as select from model.message_logs{
     ADD_SECONDS(LOCALTOUTC(sending_time,'UTC'),60*60*9) as sending_time, supplier_id, supplier_name, pur_org, sending_status
  }
}

And we can use function to_char to set the data format.

service MessageService {
  view Logs as select from model.message_logs{
     to_char(ADD_SECONDS(LOCALTOUTC(sending_time,'UTC'),60*60*9),'YYYY/MM/DD HH24:MI:SS') as sending_time_format:String, supplier_id, supplier_name, pur_org, sending_status
  }
}

Because to_char where change data’s type  to String, so we must declare the column type to String  use”: String”.

Since CDS supports HANA SQL, we did the transformation using the functions of HANA SQL.

Through the above way we have completed the time zone conversion and formatting.

Hope the above content can help you. thanks for your read.