from_unixtime 和unix_timestamp日期转化

一、from_unixtime 和unix_timestamp 如下：

 1，  select from_unixtime(unix_timestamp(data_datetime)-unix_timestamp(data_datetime)%300) as tm,

           bussinessname,

          sum(upload_size_bt) as band,

          count(1)

     from dcdn_flume.livetraceplay ori 

 

  select from_unixtime(stime-stime%300) as tm,

                                server_ip,

                                client_isp,

                                client_pro,

                                bt

                         from  dcdn_mdl.f_i5_live_panda_kandun_idc

 

2，from_unixtime(starttime)= '2017-6-19 19:50:00' and from_unixtime(starttime) = '2017-6-19 19:55:00'

3，select (case when substr(domain,1,1)='d' then trunc(cast(stime+28800 as timestamp), 'MI') else trunc(cast(stime as timestamp), 'MI') end) as sstime,
              domain,
              app,
              stream,
              remoteip cip,
              localip sip,
              sum(1) cnt,
              avg(avg_frame_rate) avg_frame
         from live_orig.o_live_mixcdn_trace
        where ptime>=20180228000000
          and ptime< 20180301000000
          and ((substr(domain,1,1)='d' and stime + 28800 >= unix_timestamp('{st}') and stime + 28800 < unix_timestamp('{et}'))
          or (substr(domain,1,1) !='d' and stime >= unix_timestamp('{st}') and stime < unix_timestamp('{et}')))
          and logtype='Play_State'
          and stime is not null
          and localip is not null
          and localip !=''
        group by sstime,domain,app,stream,remoteip,localip

4、AND tm  >= unix_timestamp(cast('{st}' as TIMESTAMP)- interval 8 hours )

AND tm  <  unix_timestamp(cast('{et}' as TIMESTAMP)- interval 8 hours )

二、cast as
当将非数字类型的 char、nchar、varchar 或 nvarchar 数据转换为 int、float、numeric 或 decimal时，SQL Server 将返回错误信息。
当将空字符串 (" ") 转换为 numeric 或 decimal 时，SQL Server 也将返回错误信息

转载于:https://www.cnblogs.com/lzfy/p/8485362.html