数据仓库服务 GAUSSDB(DWS)-EXTRACT:epoch

时间:2024-11-28 17:39:00

epoch

  • 如果source为timestamp with time zone,表示自1970-01-01 00:00:00-00 UTC以来的秒数(结果可能是负数);

    如果source为date和timestamp,表示自1970-01-01 00:00:00-00当地时间以来的秒数;

    如果source为interval,表示时间间隔的总秒数。

    1
    2
    3
    4
    5
    SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
      date_part   
    --------------
     982384720.12
    (1 row)
    
    1
    2
    3
    4
    5
    SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
     date_part 
    -----------
        442800
    (1 row)
    
  • 将epoch值转换为时间戳的方法。
    1
    2
    3
    4
    5
    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second' AS RESULT;
              result          
    ---------------------------
     2001-02-17 12:38:40.12+08
    (1 row)
    
support.huaweicloud.com/sqlreference-830-dws/dws_06_0310.html