问题:

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.

回答1:

@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();

回答2:

U-SQL is using C# for the expression language, so you can use C#/.NET to do it.

Here is a link answering how to do it in C#: How can I convert a Unix timestamp to DateTime and vice versa?

Since we are currently on .NET Runtime 4.5.2, you will not be able to use the 4.6 built-in method (we plan to upgrade to a newer version of the runtime, but I do not have an ETA yet).

If you want to avoid writing the transformation inline as a C# expression you can either deploy it via VS's code behind, with a U-SQL Func variable or create and register an assembly containing the UDF.