Java 访问PI 数据库：（3）PI的基本SQL语句

从PI 获取数据

目前从PI 获取的数据

历史时间点的

• 获取快照值

SELECT tag, time, value, svalue, status FROM piarchive..picomp WHERE time = '*'或select tag,time,value,status from piarchive..pisnapshot

• 获取历史时间点值

    SELECT TOP 1 *            FROM piarchive..picomp2                     WHERE tag = 'cdm158' AND time <= '2017/9/21 22:56:00'            ORDER BY tag, time DESC '*'

• 获取历史时间点值（最近）

select * from piarchive..picomp2where tag = 'cdm158' AND time = (SELECT CASE WHEN (mytime - prevtime) < (nexttime - mytime) THEN prevtime ELSE nexttime ENDFROM(    SELECT mytime,        (            SELECT TOP 1 time            FROM piarchive..picomp2                     WHERE tag = p.mytag AND time <= p.mytime            ORDER BY tag, time DESC        ) prevtime,        (            SELECT TOP 1 time            FROM piarchive..picomp2            WHERE tag = p.mytag AND time >= p.mytime            ORDER BY tag, time ASC        ) nexttime    FROM    (        SELECT 'cdm158' mytag, DATE('2017/9/21 22:56:00') mytime    ) p) t)

• 获取当前时间

SELECT DATE(N'*') Time--2017/9/22 11:25:34

• 获取当前时间前一个小时

SELECT DATE(N'*-1h') Time--2017/9/22 10:25:11

• 获取当前日期 昨日0点

SELECT DATE(N'y') Time--2017/9/21 0:00:00

• 获取当前日期 今日0点

SELECT DATE(N't') Time--2017/9/22 0:00:00

• 获取指定日期

SELECT DATE(N'2017-09-23 00:01:12') Time--2017/9/23 0:01:12SELECT DATE(N'01-Jan-2010') Time--2010/1/1 0:00:00

• 格式化日期

select FORMAT('2017/9/21 22:56:00','yyyy-MM-dd hh:mm:ss') Time--2017-09-21 10:56:00