CODEWITHSUNDEEP
sqlnetezza
niczky12
niczky12

Reputation: 5063

Add hours to timestamp in Netezza based on other column

I'm working in a Netezza SQL database. I have 2 columns:

What I would like to do is add the TIME_OFFSET column to the DATETIME1 column as an hour.

I created the following reproducible example:

SELECT *
FROM ADMIN.INTEGRAL_ADSCIENCE_1845

WITH my_table AS (
SELECT TIMESTAMP('2017-06-01 08:01:45') AS datetime1,
    1 AS time_offset
UNION 
SELECT TIMESTAMP('2017-06-01 08:03:45') AS datetime1,
    2 AS time_offset
)
SELECT
    DATETIME1,
    TIME_OFFSET,
    DATETIME1 + TIME_OFFSET AS simple_add,
    DATETIME1 + INTERVAL '1 hour' AS add_one_hour
FROM my_table;

This creates the following output:

+---------------------+-------------+------------+---------------------+
|      DATETIME1      | TIME_OFFSET | SIMPLE_ADD |    ADD_ONE_HOUR     |
+---------------------+-------------+------------+---------------------+
| 2017-06-01 08:01:45 |           1 | 2017-06-02 | 2017-06-01 09:01:45 |
| 2017-06-01 08:03:45 |           2 | 2017-06-03 | 2017-06-01 09:03:45 |
+---------------------+-------------+------------+---------------------+

But what I would like to have is adding 1 hour to first row and 2 hours to second row.

I'm aware of the mysql date_add() function, but I'm limited to Netezza unfortunately.

Upvotes: 1

Views: 5572

Answers (1)

joebeeson
joebeeson

Reputation: 4366

This should work for you.

  WITH  my_table AS (SELECT    TIMESTAMP('2017-06-01 08:01:45') AS dttm
                             , 1 AS tm_offset
                      UNION
                     SELECT    TIMESTAMP('2017-06-01 08:03:45') AS dttm
                             , 2 AS tm_offset)
SELECT    dttm
        , tm_offset
        , dttm + tm_offset AS simple_add
        , dttm + CAST(tm_offset || ' hour' AS INTERVAL)
  FROM  my_table;

Upvotes: 2

Related Questions