Informix FROM_UNIXTIME的替代方法 [英] Informix FROM_UNIXTIME alternative
问题描述
我正在寻找一种使用表中定义的日期按间隔(例如，每30分钟)对数据进行分组的方法，因此我需要将该日期时间转换为毫秒，以便可以将其除以所需的间隔就像在这个查询中一样
I was searching for a way to group data by interval (ex: every 30 minutes) using the date defined in that table, so i need to convert that date time to milliseconds so that i can divide it by the interval i need like in this query
SELECT FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60 * 1000)) AS timekey
FROM table
GROUP BY timekey;
此查询在SQL Server上运行完美，但在notifyix上却给我错误
This query is running perfectly on SQL Server but on informix it's giving me the error
Routine (unix_timestamp) can not be resolved.
由于未在IBM Informix服务器中定义.
因此，我需要一种直接的方法来获取IBM notifyix服务器中timestamp DATETIME YEAR到FRACTION(3)列的纪元unix时间，例如SQL Server中的"UNIX_TIMESTAMP".
As it's not defined in IBM Informix server.
So i need a direct way to get epoch unix time from timestamp DATETIME YEAR TO FRACTION(3) column in IBM informix server like 'UNIX_TIMESTAMP' in SQL server.
推荐答案
如果timestamp列的类型为DATETIME YEAR TO SECOND或类似类型，则可以将其转换为DECIMAL(18,5)秒数，因为Unix Epoch，又名1970-01-01 00:00:00Z(UTC；时区偏移量+00:00)，其使用过程如下:
If the timestamp column is of type DATETIME YEAR TO SECOND or similar, then you can convert it to a DECIMAL(18,5) number of seconds since the Unix Epoch, aka 1970-01-01 00:00:00Z (UTC; time zone offset +00:00) using a procedure such as this:
{
# "@(#)$Id: tounixtime.spl,v 1.6 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure TO_UNIX_TIME written by Jonathan Leffler (previously
# jleffler@informix.com and now jleffler@us.ibm.com). Includes fix for
# bug reported by Tsutomu Ogiwara <Tsutomu.Ogiwara@ctc-g.co.jp> on
# 2001-07-13. Previous version used DATETIME(0) SECOND TO SECOND
# instead of DATETIME(0:0:0) HOUR TO SECOND, and when the calculation
# extended the shorter constant to DATETIME HOUR TO SECOND, it added the
# current hour and minute fields, as documented in the Informix Guide to
# SQL: Syntax manual under EXTEND in the section on 'Expression'.
# Amended 2002-08-23 to handle 'eternity' and annotated more thoroughly.
# Amended 2002-09-25 to handle fractional seconds, as companion to the
# new stored procedure FROM_UNIX_TIME().
#
# If you run this procedure with no arguments (use the default), you
# need to worry about the time zone the database server is using because
# the value of CURRENT is determined by that, and you need to compensate
# for it if you are using a different time zone.
#
# Note that this version works for dates after 2001-09-09 when the
# interval between 1970-01-01 00:00:00+00:00 and current exceeds the
# range of INTERVAL SECOND(9) TO SECOND. Returning DECIMAL(18,5) allows
# it to work for all valid datetime values including fractional seconds.
# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
# -62135596800 (11 digits). Both these values are unrepresentable in
# 32-bit integers, of course, so most Unix systems won't handle this
# range, and the so-called 'Proleptic Gregorian Calendar' used to
# calculate the dates ignores locale-dependent details such as the loss
# of days that occurred during the switch between the Julian and
# Gregorian calendar, but those are minutiae that most people can ignore
# most of the time.
}
CREATE PROCEDURE to_unix_time(d DATETIME YEAR TO FRACTION(5)
DEFAULT CURRENT YEAR TO FRACTION(5))
RETURNING DECIMAL(18,5);
DEFINE n DECIMAL(18,5);
DEFINE i1 INTERVAL DAY(9) TO DAY;
DEFINE i2 INTERVAL SECOND(6) TO FRACTION(5);
DEFINE s1 CHAR(15);
DEFINE s2 CHAR(15);
LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY;
LET s1 = i1;
LET i2 = EXTEND(d, HOUR TO FRACTION(5)) -
DATETIME(00:00:00.00000) HOUR TO FRACTION(5);
LET s2 = i2;
LET n = s1 * (24 * 60 * 60) + s2;
RETURN n;
END PROCEDURE;
一些关于电子邮件地址的评论不再有效-自从我写这篇文章以来的十五年里，情况发生了变化.
Some of the commentary about email addresses is no longer valid – things have changed in the decade and a half since I wrote this.
这篇关于Informix FROM_UNIXTIME的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
