У меня есть тип данных временной метки в базе данных в формате 24-JuL-11 10.45.00.000000000 AM, и я хочу преобразовать ее в временную метку unix, как я могу ее получить?
Преобразование типа данных временной метки в временную метку unix Oracle
Ответы (8)
Этот вопрос во многом противоположен Преобразование Unixtime в Datetime SQL (Oracle)
Как говорит Джастин Кейв:
Встроенных функций нет. Но его относительно легко написать. Поскольку временная метка Unix представляет собой количество секунд с 1 января 1970 г.
Поскольку вычитание одной даты из другой даты приводит к количеству дней между ними, вы можете сделать что-то вроде:
create or replace function date_to_unix_ts( PDate in date ) return number is
l_unix_ts number;
begin
l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24;
return l_unix_ts;
end;
Поскольку в секундах с 1970 года количество долей секунды не имеет значения. Вы все равно можете вызвать его с типом данных с отметкой времени...
SQL> select date_to_unix_ts(systimestamp) from dual;
DATE_TO_UNIX_TS(SYSTIMESTAMP)
-----------------------------
1345801660
В ответ на ваш комментарий, извините, но я не вижу такого поведения:
SQL> with the_dates as (
2 select to_date('08-mar-12 01:00:00 am', 'dd-mon-yy hh:mi:ss am') as dt
3 from dual
4 union all
5 select to_date('08-mar-12', 'dd-mon-yy')
6 from dual )
7 select date_to_unix_ts(dt)
8 from the_dates
9 ;
DATE_TO_UNIX_TS(DT)
-------------------
1331168400
1331164800
SQL>
Разница 3600 секунд, то есть 1 час.
Я понимаю, что ответ уже принят, но я думаю, что следует четко указать, что функция в этом ответе не учитывает переданное смещение часового пояса даты. Правильная временная метка Unix должна рассчитываться по Гринвичу (+0). Функция Oracle to_date
предполагает, что переданная дата находится в местном часовом поясе, если не указано иное. Эта проблема усугубляется тем фактом, что летнее время — это реальная вещь. Я решил эту проблему со следующей функцией:
create or replace
function unix_time_from_date
(
in_date in date,
in_src_tz in varchar2 default 'America/New_York'
)
return integer
as
ut integer := 0;
tz varchar2(8) := '';
tz_date timestamp with time zone;
tz_stmt varchar2(255);
begin
/**
* This function is used to convert an Oracle DATE (local timezone) to a Unix timestamp (UTC).
*
* @author James Sumners
* @date 01 February 2012
*
* @param in_date An Oracle DATE to convert. It is assumed that this date will be in the local timezone.
* @param in_src_tz Indicates the time zone of the in_date parameter.
*
* @return integer
*/
-- Get the current timezone abbreviation (stupid DST)
tz_stmt := 'select systimestamp at time zone ''' || in_src_tz || ''' from dual';
execute immediate tz_stmt into tz_date;
select
extract(timezone_abbr from tz_date)
into tz
from dual;
-- Get the Unix timestamp
select
(new_time(in_date, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (86400)
into ut
from dual;
return ut;
end unix_time_from_date;
У меня есть несколько сопутствующих функций, unix_time
и unix_time_to_date
, которые доступны по адресу http://jrfom.com/2012/02/10/oracle-and-unix-timestamps-revisited/. Я не могу поверить, что Oracle дошел до версии 11g, не внедрив их.
SELECT ... INTO FROM dual
. NEW_TIME поддерживает только небольшое подмножество всех часовых поясов, поэтому это может потерпеть неудачу. Это также не работает, если введенная дата не относится к текущему сезону, попробуйте unix_time_from_date(SYSDATE + 150)
.
- person Wernfried Domscheit; 26.06.2019
на дату:
FUNCTION date_to_unix (p_date date,in_src_tz in varchar2 default 'Europe/Kiev') return number is
begin
return round((cast((FROM_TZ(CAST(p_date as timestamp), in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60));
end;
для метки времени:
FUNCTION timestamp_to_unix (p_time timestamp,in_src_tz in varchar2 default 'Europe/Kiev') return number is
begin
return round((cast((FROM_TZ(p_time, in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60));
end;
Я использую следующий метод, который немного отличается от других ответов тем, что он использует функцию sessiontimezone()
для правильного получения даты
select
(
cast((FROM_TZ(CAST(in_date as timestamp), sessiontimezone) at time zone 'GMT') as date) -- in_date cast do GMT
-
TO_DATE('01.01.1970','dd.mm.yyyy') -- minus unix start date
)
* 86400000 -- times miliseconds in day
from dual;
Вот что я придумал:
select substr(extract(day from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 24 * 60 * 60 +
extract(hour from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 * 60 +
extract(minute from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 +
trunc(extract(second from (n.origstamp - timestamp '1970-01-01 00:00:00')),0),0,15) TimeStamp
from tablename;
FWIW
Для преобразования между временем Oracle и временем Unix я использую эти функции. Они учитывают ваш текущий часовой пояс. Вам также следует добавить ключевое слово DETERMINISTIC
, например, если вы хотите использовать такую функцию в функциональном индексе. Преобразование между DATE
и TIMESTAMP
должно выполняться Oracle неявно.
FUNCTION Timestamp2UnixTime(theTimestamp IN TIMESTAMP, timezone IN VARCHAR2 DEFAULT SESSIONTIMEZONE) RETURN NUMBER DETERMINISTIC IS
timestampUTC TIMESTAMP;
theInterval INTERVAL DAY(9) TO SECOND;
epoche NUMBER;
BEGIN
timestampUTC := FROM_TZ(theTimestamp, timezone) AT TIME ZONE 'UTC';
theInterval := TO_DSINTERVAL(timestampUTC - TIMESTAMP '1970-01-01 00:00:00');
epoche := EXTRACT(DAY FROM theInterval)*24*60*60
+ EXTRACT(HOUR FROM theInterval)*60*60
+ EXTRACT(MINUTE FROM theInterval)*60
+ EXTRACT(SECOND FROM theInterval);
RETURN ROUND(epoche);
END Timestamp2UnixTime;
FUNCTION UnixTime2Timestamp(UnixTime IN NUMBER) RETURN TIMESTAMP DETERMINISTIC IS
BEGIN
RETURN (TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixTime * INTERVAL '1' SECOND) AT LOCAL;
END UnixTime2Timestamp;
Я согласен с тем, что Вернфрид Домшайт и Джеймс Самнерс объяснял в своих постах решения - в основном из-за проблем с часовым поясом и летним/зимним временем!
Одна из функций, которую я предпочитаю короче и без динамического SQL:
-- as Date
CAST ( FROM_TZ( TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL(input_date , 'SECOND') , 'GMT' ) AT TIME ZONE 'Europe/Berlin' AS DATE )
or
-- as Timestamp
FROM_TZ( to_timestamp(Date '1970-01-01' + input_date / 86400 ), 'GMT' ) AT TIME ZONE 'Europe/Berlin'
В качестве часового пояса нужно указать статическую строку (например, «Европа/Берлин»), а не переменную dbtimezone или sessiontimezone, потому что это может привести к неправильному смещению, поскольку время выполнения может быть летом, а временная метка unix может быть зимой.