I have the following query:
SEQUENCED VALIDTIME PERIOD '(2017-11-06,2017-11-10)'
SELECT a, b, c,
d, e, f
FROM table_1
This creates a table with columns a, b, c, d, e, f and VALIDTME. The VALIDTIMEcolumn contains data similar to (2017-11-09 07:17:03.120000-05:00, 2017-11-10 00:00:00.000000-05:00). How can I modify my query to obtain this data as 11/09/2017 07:17:03, 11/10/2017 00:00:00? Ideally, I would like to split this timestamp as two columns: start time and end time. However, the techniques that work on normal columns doesn't seem to work on VALIDTIME. Kindly help.
Your temporal table should have a column with a PERIOD data type designated as your VALIDTIME column. I think there are helper functions you can use with the VALIDTIME column, like BEGIN() and END(), to extract the values you want. Once you do that, you should be able to format however you like. Something like:
SELECT a,b,c,d,e,f,
CAST(BEGIN(ValidTime_Column) AS DATE FORMAT 'MM/DD/YYYY HH:MM:SS'),
CAST(END(ValidTime_Column) AS DATE FORMAT 'MM/DD/YYYY HH:MM:SS')
FROM table_1
I don't have a TD system to check, so you may get a syntax error. Give it a try and let me know if it works.