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

soumitrabsoumitrab Vertica Employee

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 ?

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    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
    
  • soumitrabsoumitrab Vertica Employee

    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

  • marcothesanemarcothesane - Select Field - Administrator

    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
    
  • marcothesanemarcothesane - Select Field - Administrator

    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' .

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file