# Php连接Oracle问题
# 时间转换
文章引用；
Php中常用的是时间戳，如何转换为oracle中Date类型？
# 使用oracle函数
在oracle中新建两个函数:unixts_to_date和date_to_unixts。
# unixts_to_date
CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* Converts a UNIX timestamp into an Oracle DATE
*/
unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
oracle_date DATE;
BEGIN
IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
'UNIX timestamp too large for 32 bit limit'
);
ELSIF unixts < min_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
'UNIX timestamp too small for 32 bit limit' );
ELSE
oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
END IF;
RETURN (oracle_date);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# date_to_unixts
CREATE OR REPLACE
FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
/**
* Converts an Oracle DATE to a UNIX timestamp
*/
unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
unix_ts PLS_INTEGER;
BEGIN
IF oracle_date > max_date THEN
RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
ELSIF oracle_date < min_date THEN
RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
ELSE
unix_ts := (oracle_date - unix_epoch) / (1/86400);
END IF;
RETURN (unix_ts);
END;
The following query shows how it might be used:
SELECT
ename,
TO_CHAR(hiredate, 'YYYY') AS hired_year,
TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
TO_CHAR(hiredate, 'ddth Mon, YYYY') AS hired_pretty
date_to_unixts(hiredate) AS hired_unixts
FROM
emp
ORDER BY
hiredate
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 实际查询
使用如下sql查询：
select mmsi,to_char(time,'YYYY-MM-DD HH24:MI:SS') as mydatetime,date_to_unixts(time) as timestamp from tjg_portnet.DiaoDu_ShipJoin where unixts_to_date(1621061672) < time and unixts_to_date(1621061687) > time
1
上面查询时间很长，目前不知道原因。
# php解决方案
由于上面使用存储函数因未知原因导致查询无法进行，后面使用的是纯php直接转换日期再进行查询：
class timeTool{
static function getOracleDateFromTimestamp($timestamp){
return "TO_DATE('".date("Y-m-d H:i:s", (int)$timestamp)."','YYYY-MM-DD HH24:MI:SS')";
}
static function getTimeFromOracleDate($timeField,$asTimeField='datetime'){
return "to_char($timeField,'YYYY-MM-DD HH24:MI:SS') as $asTimeField";
}
static function setCurrentTimeZone(){
ini_set('date.timezone','Asia/Shanghai');
}
static function displayCurrentTime(){
self::setCurrentTimeZone();
echo "当前时间为：".date('Y-m-d H:i:s', time())."<br>";
}
}
if(request::getInstance()->isPost()) {
$params = request::getInstance()->dealUE4PostParams();
$timeStamp1 = timeTool::getOracleDateFromTimestamp($params["timeStamp1"]);
$timeStamp2 = timeTool::getOracleDateFromTimestamp($params["timeStamp2"]);
$timeField = timeTool::getTimeFromOracleDate("time");
$result = oraclePDO::getInstance()->exec("select mmsi,$timeField from tjg_portnet.DiaoDu_ShipJoin where $timeStamp1 < time and $timeStamp2 > time");
foreach ($result as $key => $value){
$result[$key]["TIMESTAMP"] = strtotime($value["DATETIME"]);
}
debug::Log($result);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 分组排序
Oracle数据库以多列进行分组，然后组内按某列进行排序，这时需要使用OVER语法，但是它是不能直接使用的，需要和其他函数配合使用。下面是常见的3个分组排序函数:
- row_number() over(partition by col1 order by col2)：它表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。
- rank() over()：它是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。
- dense_rank() over()：它也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。