How can I get only date (excluding timestamp ) without changing timezone from timestamptz column .

soumitrab

In my vertica timestamptz column storing date with a timezone A and vertica's own time zone is B . Now when I am doing to_char(timestaptz_column, ''dd-mm-yyyy") output is always getting converted to server timezone i.e. B . But I want dd-mm-yyyy to be as is in timestaptz_column . Is that possible ?

marcothesane

Does this help?

SELECT
  CURRENT_TIMESTAMP                               AS justnow
, CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London'  AS now_in_london
;
-- out             justnow            |       now_in_london        
-- out -------------------------------+----------------------------
-- out  2020-05-30 00:49:45.048244+02 | 2020-05-29 23:49:45.048244
SELECT
  CURRENT_TIMESTAMP::DATE                                AS today
, (CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London')::DATE AS today_in_london
;
-- out    today    | today_in_london 
-- out ------------+-----------------
-- out  2020-05-30 | 2020-05-29

soumitrab

select distinct (collection_date AT TIME ZONE 'UTC' ::Timestamp) as Report_Timeline , collection_date from TABLE_1;

Report_Timeline | collection_date
2020-05-15 19:29:47 | 2020-05-16 00:59:47
2020-05-15 19:29:47 | 2020-05-16 00:59:47

Here I want Report time line to be as 2020-05-16 and not as 2020-05-15 . That means time is getting converted to the local timezone here as per server timezone while doing any operation even if I just do a to_char that will also not work .

Any way to achieve this ??

Thanks
Soumitra

marcothesane

You need to use the AT TIMEZONE clause to convert a timestamp.
With your input data:

WITH
input(report_timeline,collection_date) AS (
          SELECT TIMESTAMP '2020-05-15 19:29:47',TIMESTAMP '2020-05-16 00:59:47'
UNION ALL SELECT TIMESTAMP '2020-05-15 19:29:47',TIMESTAMP '2020-05-16 00:59:47'
)
SELECT
  report_timeline 
, report_timeline AT TIME ZONE 'Europe/Zurich' AS ts_zrh
, report_timeline AT TIME ZONE 'UTC' AS report_timeline_utc
, collection_date 
, collection_date AT TIME ZONE 'Asia/Kabul' AS collection_loc_dt 
FROM Input;
--    report_timeline   |         ts_zrh         |  report_timeline_utc   |   collection_date   |   collection_loc_dt    
-- ---------------------+------------------------+------------------------+---------------------+------------------------
--  2020-05-15 19:29:47 | 2020-05-15 19:29:47+02 | 2020-05-15 21:29:47+02 | 2020-05-16 00:59:47 | 2020-05-15 22:29:47+02
--  2020-05-15 19:29:47 | 2020-05-15 19:29:47+02 | 2020-05-15 21:29:47+02 | 2020-05-16 00:59:47 | 2020-05-15 22:29:47+02

I live around Zurich - and the maths I performed on your data suggest a timezone around Kabul (4:30 off GMT) for your example.
Now, all you have to do, to get the date, is then to hard-cast the time for Kabul(?) to a DATE using the double-colon operator:

WITH
input(report_timeline,collection_date) AS (
          SELECT TIMESTAMP '2020-05-15 19:29:47',TIMESTAMP '2020-05-16 00:59:47'
UNION ALL SELECT TIMESTAMP '2020-05-15 19:29:47',TIMESTAMP '2020-05-16 00:59:47'
)
SELECT
  report_timeline 
, report_timeline AT TIME ZONE 'Europe/Zurich' AS ts_zrh
, report_timeline AT TIME ZONE 'UTC' AS report_timeline_utc
, collection_date::DATE
, (collection_date AT TIME ZONE 'Asia/Kabul')::DATE AS collection_loc_dt 
FROM Input;
--   report_timeline   |         ts_zrh         |  report_timeline_utc   | collection_date | collection_loc_dt 
-----------------------+------------------------+------------------------+-----------------+-------------------
-- 2020-05-15 19:29:47 | 2020-05-15 19:29:47+02 | 2020-05-15 21:29:47+02 | 2020-05-16      | 2020-05-15
-- 2020-05-15 19:29:47 | 2020-05-15 19:29:47+02 | 2020-05-15 21:29:47+02 | 2020-05-16      | 2020-05-15

marcothesane

You can change the timezone of your client application and check the different behaviour:
Check the time zone:

show timezone;
name    |setting
timezone|Europe/Vaduz

Change the time zone:

set timezone 'Europe/Zurich';
SET

This setting lives during your session, and you'll have to set it at login time.
See if you get the desired behaviour, then by just type casting the timestamp to timestamptz, then to date.
Note that cast-ing to timestamptz with a different timezone needs an input literal reflecting the time zone, like:
'2020-01-01 17:05+00'::TIMESTAMPTZ . This one returns a different value if the time zone is 'Europe/London' or the time zone is 'Europe/Zurich' .