Casting Between Temporal Data Types

The following examples show how to use SciDB conversion functions to convert temporal data types.

Cast datetime to datetimetz

Use append_offset when your date and time are the correct local time, but you want to add timezone information.  This effectively casts a datetime attribute to a datetimetz.

1. Create an array with a datetime attribute, and load it with the current time:

   AFL% create array T<dt:datetime>[i=0:0];
   Query was executed successfully
   AFL% store(build(T, now()), T);
   {i} dt
   {0} '2016-02-25 23:02:55'

2. Apply a new attribute using append_offset to set the timezone offset.  The offset argument is in seconds, so -18000 is a five hour offset.  Note that the date and time portions of the value do not change, only the timezone offset:
   

   AFL% apply(T, dtz, append_offset(dt, -18000)); 
   {i} dt,dtz
   {0} '2016-02-25 23:02:55','2016-02-25 23:02:55 -05:00'

Append an Offset and Apply it to the Time

Use apply_offset to convert a UTC datetime to the correct local time, adding timezone information.  The timezone offset is in seconds.

This query shows the difference between append_offset and apply_offset:

AFL% store(apply(T, est, apply_offset(dt,-18000)), T1);
{i} dt,est
{0} '2016-02-25 23:02:55','2016-02-25 18:02:55 -05:00'

Return the datetime portion of datetimetz

To return the datetime portion of a datetimetz value, use the strip_offset function as follows:

AFL% project(apply(T1, est_no_offset, strip_offset(est)), est, est_no_offset);
{i} est,est_no_offset
{0} '2016-02-25 18:02:55 -05:00','2016-02-25 18:02:55' 

Apply an Offset to datetimez and Return a GMT datetime

To apply the offset to the datetime and return a GMT datetime, use the togmt function as follows:

AFL%  apply(T1, gmt, togmt(est));
{i} dt,est,gmt
{0} '2016-02-25 23:02:55','2016-02-25 18:02:55 -05:00','2016-02-25 23:02:55' 

Remove the arrays:

AFL% remove(T); remove(T1);