Convert unix time to date time 4

[STENSTRUP]

How can I convert a number from Unix time (1.018.597.109) to a date time I can recognize in crystal?

 

[dgillz]

I don't know how unix time works. What components of that string represent Year, month and day, and how do you read them? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[MJSQA]

Actually, unix time should not have the periods. It is a count of the seconds since 1/1/1970. To calculate the date here is the formula:

dateTimeVar originaldate := DateTime (1970, 01, 01);
originaldate + ({unixtime}/86400)

I divide the time by the number of seconds in a day and add that to the original date that unix is based off of. I tested this formula against a converter I found on the web and it is accurate down to the second.
If you are receiving the unix time with periods then you need to use some other formula to remove those periods first.

 

[STENSTRUP]

Thanks a lot - it works. Now I just have to find a solution for handling summertime in Europe. The results I get are 1 hour or 2 hours wrong, but I think I can manage. I have seen a formula in Microsoft Acces to convert a number from UNIX time to a date time and I would have expected a function/formula in crystal who did the same. But anyway I can get on working now.

 

[dgillz]

Stenstrup-

You might consider writing a UFL - user function library - for this and add it as a user defined function in Crystal. You can create user defined functiond for crystal reports in several anguages including VB and C++. George Peck's book has an excellent section regarding how to do this. Given the proper logic you can most ikely get the european hours worked out as well with a user defined function.

Let me know if you have any questions. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SchuhTL]

I have had a similar problem in the past. I have used this formula and it works great! (#1/1/1970#+({UnixTimeField}/(60*60*24))-(6/24)) This is based on GMT so you will have to adjust the part of the formula that reads (6/24) accordingly. I would start by changing (6/24) to (2/24) and see if that is close. If is is not, keep changing the (6/24) up or down until you find the time zone that you need. I hope this helps.

 

[khcm8jw]

I feel so lame,

I have the formula, but how do I use it in Crystal to output it in a report?

 

[synapsevampire]

Try:

Insert->Field Object->Right click Formula Fields->New

Add you formula in there.

Now you can drag the field onto your report.

-k

 

[Reebo99]

Use:

DateAdd("s",{unixtime},DateTime(1970,01,01,00,00,00))

This accumulates the DateTime by adding the seconds.



Reebo
Scotland (Sunny with a Smile)

 

[bdjb]

Hello,
I found a UFL at

http://www.elegantsp.com/crystalfunc.html

that is free and does the conversion for you. I use it to convert Remedy date/time, and have had no problems.

Bob

What happens if you get scared half to death twice?

 

[Flopper]

Greetings,

I've had to create a custom function to allow for this... the formula below calculates for British Summer Time so you might have to a variable or 2. An explanation is included.

------------------
Function (NumberVar UnixDate);
//Converts Unix number to date.
DateTimeVar UnixDt:= DateAdd("s",UnixDate,DateTime(1970,01,01,00,00,00));

//BST presumed to be from the last Sunday in March @ 01:00 until the last Sunday in October @ 01:00.
//Spring Forward - Fall Back

//Calculates the Start of BST in UnixDt year.
Datevar LastDayMar:=Date(Year(UnixDt),04,01)-1;
DateVar BSTStartDt:=If DayofWeek(LastDayMar)<>1 then LastDayMar-(DayOfWeek(LastDayMar)-1) else LastDayMar;
DateTimeVar BSTStart:=DateTime(Year(UnixDt),03,Day(BSTStartDt),02,00,00);

//Calculates the End of BST in UnixDt year.
Datevar LastDayOct:=Date(Year(UnixDt),11,01)-1;
DateVar BSTEndDt:=If DayofWeek(LastDayOct)<>1 then LastDayOct-(DayOfWeek(LastDayOct)-1) else LastDayOct;
DateTimeVar BSTEnd:=DateTime(Year(UnixDt),10,Day(BSTEndDt),00,00,00);

//Returns DateTime depending on BST.
If UnixDt in BSTStart to BSTEnd then DateAdd('h',1,UnixDt) else UnixDt;

//BST Date checks for future reference.
//2000 Sun 26/03/2000 01:00 Sun 29/10/2000 01:00
//2001 Sun 25/03/2001 01:00 Sun 28/10/2001 01:00
//2002 Sun 31/03/2002 01:00 Sun 27/10/2002 01:00
//2003 Sun 30/03/2003 01:00 Sun 26/10/2003 01:00
//2004 Sun 28/03/2004 01:00 Sun 31/10/2004 01:00
//2005 Sun 27/03/2005 01:00 Sun 30/10/2005 01:00
//2006 Sun 26/03/2006 01:00 Sun 29/10/2006 01:00