SQL query date according to time zone

Question

We use a Vertica database with table columns of type timestamptz, all data is inserted in accordance with the UTC time zone. We use spring-jdbc NamedParameterJdbcTemplate

All requests are based on full calendar days, for example. the start date is 2013/08/01 and the end date is 2013/08/31, which brings everything between "2013/08/01 00: 00: 00.0000" and "2013/08/31 23: 59: 59.9999"

We are trying to modify our queries to consider time intervals, that is, I can for my local time zone, I can ask "2013/08/01 00: 00: 00.0000 Asia / Jerusalem" until "2013/08/31 23:59: 59.9999 Asia / Jerusalem ", which is clearly different from" 2013/08/01 00: 00: 00.0000 UTC 'to' 2013/08/31 23: 59: 59.9999 UTC '.

So far I can’t find a way to do this, I tried to set the time zone in the session:

set the time zone for Asia / Jerusalem;

This does not even work in my database client.

Computing the difference in our Java code will not work for us, since we also have queries that return date groups (this is completely confused).

Any ideas or recommendations?

Answer 1

Ok, so obvious:

set the time zone to "Asia / Jerusalem";

worked, and I just didn't realize it, but in order to help others, I'm going to add something else that works:

select fiels in the Asia / Jerusalem time zone from my_table;

will work for timestamptz fields

Answer 2

I am not familiar with Veritca, but some general tips:

• It is generally best to use half-open intervals for date range queries. The start date must be inclusive, and the end date must be exclusive. In other words:

  start <= date < end 

  or

   start <= date && end > date 

  The end date will not be '2013/08/31 23:59:59.9999' , instead it will be the beginning of the next day or '2013/09/01 00:00:00.0000' . This avoids the problems associated with decimal precision.

  This example is for finding a single date. Since you are requesting a date range, you have two inputs. So it would be:

   startFieldInDatabase >= yourStartParameter AND endFieldInDatabase < yourEndParameter 

  Again, you must first increase the value of the final parameter before the start of the next day.

• It looks like Vertica knows TZ, given that you talked about timestamptz types in your answer. Assuming they are similar to the Oracle type TIMESTAMPTZ , then it looks like your solution will work fine.

• But usually, if you save time in UTC in your database, you simply convert the time for entering requests in advance. Therefore, instead of querying between '2013/08/01 00:00:00.0000' and '2013/09/01 00:00:00.0000' you should convert this ahead of time and the query between '2013/07/31 21:00:00.0000' and '2013/08/31 21:00:00.0000' . There are many posts on how to do this conversion in Java, either natively or using Joda Time , so I won’t repeat it here.

• As a note, you should make sure that any TZDB implementation that you use (Vertica, Java, or JodaTime) has the latest update 2013d , as this includes a change to Israel’s daylight saving time rule , which will take effect this year.