tags:

views:

8

answers:

1
Q: 

How can I accurately convert an INFORMIX-SE audit table column which stores UNIX timestamp[INT] into date and time?

INFORMIX-SE:

SE allows you to create an audit file for any table. The audit file has the same schema as the table being audited plus a header consisting of several columns, one of them being a_time INTEGER, which contains a UNIX timestamp of when the row was added, updated or deleted. UNIX timestamp is an INTEGER value corresponding to the number of seconds since 1970-01-01T00:00:00Z. Can anyone come up with an algorithm which can accurately convert these seconds into a date and time?

+1  A: 

Here is an algorithm that shows you how to do it: http://forums.mrplc.com/index.php?showtopic=13294&view=findpost&p=65248

lrm  2010-06-29 00:07:51
@lrm- looks good, however it's not accurate because it doesn't take into account leap seconds in UTC. My goal is to obtain accurate time of day to a precision of hh:mm so I dont know how many leap seconds have been added since 01-JAN-1970,T00:00:00Z.
Frank Computer  2010-06-29 00:21:34
@Frank: POSIX does not take leap seconds into account, period. Unless you know that your system does - which seems a tad unlikely - don't worry about it. SE most certainly does not take leap seconds into account directly; it just relies on the system clock to tell it a number.
Jonathan Leffler  2010-06-29 00:58:44

related questions

Does informix have a "materialized view" equivalent or DB-table syncing
Informix JDBC timestamp string format
Database alias in Informix
SQL character field concatenation (without using CONCAT() or +)
How do I get constraint details from the name in Informix?
What SQL query shows me the tables and indexes used by a view on Informix?
Informix SQL query: Two similar queries returning different results
Insert into temp values (select.... order by id)
Informix: how to get an id of the last inserted record
Informix certification for developer
Converting a String to HEX in SQL
Informix for beginner
Connecting to Informix database from .Net
Casting in Informix
what is the max length of an informix column and can it be increased?
what does "Total Length of columns in constaint is too long" err mean in Informix?
Setting PDQ inside an SPL - local scope?
Informix SQL Syntax - Nest Count, Sum, Round
Delphi 2009 and Informix dbExpress problem with Windows 2003
Row numbers for a query in informix
Attaching Informix .dat and .idx files
What is th easiest way to copy a database from one Informix IDS 11 Server to another
Find long running query on Informix?
SQL Insert into ... values ( SELECT ... FROM ... )
Sleep from within an Informix SPL procedure