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

Question

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.

score 3 · Accepted Answer · answered Apr 10 '18 at 20:37

3

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

answered Apr 10 '18 at 20:37

David Paul Giroux

657
4
10

Nice! I worked up the inline function versions [here](https://gist.github.com/wBobuk/f0e0623150903cc07b581990b13ed1fa) but your example seems much more compact. – wBob Apr 10 '18 at 21:22
Big question, how would you do this for Unix Time Stamp down to the Microseconds (i.e.: 1523048766211397)? – Fastidious Jun 04 '18 at 16:26
DateTime.AddTicks. 10 ticks is one microsecond. – David Paul Giroux Jun 05 '18 at 20:41

score 1 · Answer 2 · answered Apr 10 '18 at 19:56

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.

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

2 Answers2