SQL: Combine columns to specific datetime and cast to UTC

Question

I am using Oracle SQL. I want to convert three columns to datetime in sql.

My data looks like:

DAY (DATE)     HOUR (NUMBER)     HALFHOUR (NUMBER)
21.04.22       11                22
21.04.22       11                23
21.04.22       12                24
21.04.22       12                25
21.04.22       13                26
21.04.22       13                27
....

I need to combine each row to the following specific format, in one column:

2022-04-21T13:30:00.00Z

Moreover, it should be converted from an utc time where data comes from (like UTC+3) to UTC+0 automatically.

How do I do this? I googled a lot, but cant do it.

Thanks :)

Answer 1

The solution to your problem is:

SELECT T1.*,
TO_CHAR(FROM_TZ(TO_TIMESTAMP(DAY||' '||HOUR||':'||HALFHOUR, 'DD.MM.RR HH24:MI'), 'Europe/Berlin') AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.ff2"Z"') as time_utc
FROM T1;

TO_TIMESTAMP:

TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype. Syntax is:

TO_TIMESTAMP( string1 [, format_mask] ['nlsparam'] )

FROM_TZ:

In Oracle Database, the FROM_TZ() function converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. Pass the timestamp value and the time zone as two separate arguments, and the function returns them as a TIMESTAMP WITH TIME ZONE value. Syntax is :

FROM_TZ(timestamp_value, time_zone_value)

time_zone_value all possible values an be found out using the below query:

SELECT * FROM V$TIMEZONE_NAMES;

The output generated using above two functions is the time in the germany time-zone and is converted to UTC time zone using the " AT TIME ZONE 'UTC' "

Then using TO_CHAR it is converted to the required format.

You can see the working sample example at the below link:

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b030170a2c5536b4f80f1287bbfe4aca