How can I get only date (excluding timestamp ) without changing timezone from timestamptz column .
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 ?
0
Answers
Does this help?
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
You need to use the AT TIMEZONE clause to convert a timestamp.
With your input data:
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:
You can change the timezone of your client application and check the different behaviour:
Check the time zone:
Change the time zone:
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' .