Time Zone - DST

MaorFrid

My data base has time stamps in UTC but I work from Israel (UTC+2)
Hiw can I query my data in israel time intervals without having to manually "calculate" the daylight saving time

Here is an example of what I mean:

If I want to check the count of records in one of my tables entered between 27-Oct at 09:00 ISL to 29-OCt 09:00 ISL.
Currently I need to "remember" that in ISL the DST accurs on 28-OCT night so
First date in UTC terms is
27-Oct 07:00 until 28-Oct *08:00*

Is there any simple function that I can use?
Input - time in my own TZ
Requested Output - TZ in UTC (considering DST)

Thanks

DaveT

Vertica timestamps are stored in UTC internally so they will convert properly. Also, you can use SET TIMEZONE:

set timezone = 'Israel';
SET

select current_timestamp, current_timestamp at timezone 'utc';
?column? | timezone
-------------------------------+----------------------------
2018-11-12 23:19:37.867486+02 | 2018-11-12 21:19:37.867486
(1 row)

select ts, ts at timezone 'UTC' ts_utc from (select cast('2018-11-04 15:00:00' as timestamptz) as ts) a;
ts | ts_utc
------------------------+---------------------
2018-11-04 15:00:00+02 | 2018-11-04 13:00:00
(1 row)

select ts, ts at timezone 'UTC' ts_utc from (select cast('2018-11-03 15:00:00' as timestamptz) as ts) a;
ts | ts_utc
------------------------+---------------------
2018-11-03 15:00:00+02 | 2018-11-03 13:00:00
(1 row)

select ts, ts at timezone 'America/New_York' ts_ny from (select cast('2018-11-04 15:00:00' as timestamptz) as ts) a;
ts | ts_ny
------------------------+---------------------
2018-11-04 15:00:00+02 | 2018-11-04 08:00:00
(1 row)

select ts, ts at timezone 'America/New_York' ts_ny from (select cast('2018-11-03 15:00:00' as timestamptz) as ts) a;
ts | ts_ny
------------------------+---------------------
2018-11-03 15:00:00+02 | 2018-11-03 09:00:00
(1 row)

DaveT

I assume you have a typo above and you mean '2018-10-04 09:00' instead of '2018-10-04 19:00'.

I am assuming your data type is TIMESTAMP and not TIMESTAMPTZ. If you have some TIMESTAMPTZ data types then you will want to honor those values and probably use the previous example provided using AT TIMEZONE.

If TIMESTAMP and you either know or just want to assume the data is stored in UTC then you can write a function like this example to accept values in 'Israel' time and convert. There may be other ways to do this.

\d ts
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------+------+---------+----------+-------------+-------------
public | ts | c1 | timestamp | 8 | | f | f |
(1 row)

select * from ts order by 1;

c1

2018-10-04 12:00:00
2018-10-05 12:00:00
2018-10-27 21:00:00
2018-10-27 22:00:00
2018-10-28 00:00:00
2018-10-28 00:59:59
2018-10-28 02:00:00
2018-10-28 03:00:00
2018-11-03 23:00:00
2018-11-04 00:00:00
2018-11-04 00:59:59
2018-11-04 01:00:00
2018-11-04 02:00:00
2018-11-04 11:00:00
2018-11-04 12:00:00
2018-11-04 13:00:00
(16 rows)

create function isl_to_utc(ts timestamp) return timestamp as begin return(cast(to_char(ts)||' Israel' as timestamptz) at time zone 'UTC'); end;
CREATE FUNCTION

select c1 from ts where c1 between isl_to_utc('2018-10-28 00:00:00') and isl_to_utc('2018-11-04 03:00:00') order by c1;

c1

2018-10-27 21:00:00
2018-10-27 22:00:00
2018-10-28 00:00:00
2018-10-28 00:59:59
2018-10-28 02:00:00
2018-10-28 03:00:00
2018-11-03 23:00:00
2018-11-04 00:00:00
2018-11-04 00:59:59
2018-11-04 01:00:00
(10 rows)

You might want to check the doc for differences on TIMESTAMPTZ and TIMESTAMP behavior:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMPATTIMEZONE.htm

arindamnandidbl

You can do -

SELECT date_column AT TIMEZONE 'Israel' FROM table_name;

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMEATTIMEZONE.htm?TocPath=SQL%20Reference%20Manual|SQL%20Data%20Types|Date%2FTime%20Data%20Types|_____6

MaorFrid

First of all, thank you !
second,
can I wrap this small query as a UDF ?

I mean, for future queries I want to have something like:

select
*
from Table
where TS between
ISL_To_UTC ('2018-10-04 12:00') --> this will give me '2018-10-04 19:00'
and
ISL_To_UTC ('2018-11-04 12:00') --> this will give me '2018-11-04 10:00'

Thanks again for your quick answers