4

Is there anything in Vertica to get the same output as given by the following Sql query in Oracle?

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00', 
      'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') 
   AT TIME ZONE 'America/Los_Angeles' "West Coast Time" 
   FROM DUAL;

West Coast Time
------------------------------------------------
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES
Inian
  • 80,270
  • 14
  • 142
  • 161
Amit
  • 19,780
  • 6
  • 46
  • 54

2 Answers2

3

I was able to convert. Sample queries given below:

SELECT TIMESTAMP WITH TIME ZONE '2012-07-02 10:00:00-04' AT TIME ZONE 'GMT';
      timezone
---------------------
2012-07-02 14:00:00
(1 row)


SELECT TIMESTAMP WITH TIME ZONE '2012-07-02 14:00:00-00' AT TIME ZONE 'EDT';
      timezone
---------------------
2012-07-02 10:00:00
(1 row)
Inian
  • 80,270
  • 14
  • 142
  • 161
Amit
  • 19,780
  • 6
  • 46
  • 54
  • What if you want to convert a timestamp column and not just one specific timestamp? – elevendollar Dec 05 '19 at 10:29
  • 1
    @elevendollar : `SELECT col_name AT TIMEZONE 'America/New_York'`. See docs for details - https://www.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/InstallationGuide/AppendixTimeZones/UsingTimeZonesWithHPVertica.htm – Amit Dec 05 '19 at 13:11
0

Might be helpful .There is a function for this purpose in oracle

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2036.htm

select NEW_TIME (TO_DATE ('2011/11/11 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') from dual;
user666
  • 1,104
  • 12
  • 20