Search code examples
u-sql

Convert UTC Time Stamp to Date and Time in U-SQL

I'm fairly new to U-SQL so this may be a simple question.

I have a field, [utc_timestamp], in an ADL table with a unix time stamp in the form "1497178877" which measures the number of seconds from 1970-01-01.

Is there any easy way to convert this time stamp in U-SQL to both a date in the form of "2017-06-11" and a date time object?

My initial attempt didn't seem to work quite right.

Solution 
  • @table = 
SELECT * FROM 
    ( VALUES
    (1497178877)
    ) AS T(seconds);

DECLARE @dateStart = new DateTime(1970, 01, 01);

@result = 
SELECT  @dateStart.AddSeconds(seconds).ToString("yyyy-MM-dd") AS newDateString,
        @dateStart.AddSeconds(seconds) AS newDate
FROM @table;

OUTPUT @result
TO "/Temp/Dates/Example1.txt"
USING Outputters.Tsv();