Conversions between UNIX time and normal time

[Rbp1226]

Hi,

Is anyone having good idea how to convert UNIX time to normal DateTime in Crystal?
Example: UNIX time: 1174996028

Environment:

Crystal Reports XI
Oracle 10g


Thanks!

 

[LyleU]

To get you started, the Unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970. Therefore, the Unix time stamp is merely the number of seconds between a particular date and the Unix Epoch. Your time stamp of 1174996028, is 37.2588796293 years from 01/01/1970 or occurs sometime in 2007. It's all just math from this point onwards. Good luck.

What happens on January 19, 2038? A Y2K38 bug?
On this date the Unix Time Stamp will cease to work due to a 32-bit overflow. Before this moment millions of applications will need to either adopt a new convention for time stamps or be migrated to 64-bit systems which will buy the time stamp a "bit" more time.


Lyle
----
I reject your reality and substitute my own. (Adam Savage)

 

[lbass]

So using Lyle's explanation, I think you can use:

dateadd("s",{table.unixtime},date(1970,1,1))

-LB

 

[LyleU]

Cool. Being a crystal rookie, and lurker in the crystal forums, I would have made the whole process much more difficult.


Lyle
----
I reject your reality and substitute my own. (Adam Savage)

 

[rheard]

Cool. Being a crystal rookie, and lurker in the crystal forums, I would have made the whole process much more difficult.

Just to make things more difficult... are you based in the UK? If so, we use the following code to allow for Daylight Savings Clock Changes in Spring/Autumn...

local datetimevar rec_date := DateAdd("s",{table.datefield},DateTime(1970,01,01));
local datetimevar begin_date := datetime(year(rec_date),1,1,2,0,0);
local datetimevar work_date := dateadd("m",3,begin_date);
local datetimevar DLSStart := work_date - dayofweek(work_date) + 1;
work_date := dateadd("m",10,begin_date);
local datetimevar DLSEnd   := work_date - dayofweek(work_date) + 1; 

//if in DLS Timeframe, add 1 hour
if rec_date in DLSStart to DLSEnd then
local datetimevar result_date := DateAdd("h",+1,rec_date) else 
local datetimevar result_date := rec_date;

result_date;

Russ

 

[rheard]

Umm, just thought - I should perhaps explain why we need to convert DLS times...
Our data source (CA-USD) stores date/times in unix, but the browser app that allows the users to manage their incident/change tickets will 'helpfully' examine your pc clock to determine your timezone and convert the datetime to the correct local time when you view the records.

Therefore, reporting has to do the same to the datetime fields so they appear the same as on the screen.

It gets better... this datasource is based here in the UK, but is used globally - so users in different timezones will see different times on the same field on the same ticket.

Russ

 

[Rbp1226]

Hi all,

I am based in US, I am using Lbass’s formula and it works good, thank you much for all your help!!


Rbp

 

[goduke]

All,

I am running into the same issues. I also have dates that are stored in unix and I am easily able to convert them into central time using the formula below, but I have to adjust them whenever daylight savings time hits.

dateadd("h",-4,dateadd("s",{chg.sched_start_date},date(1970,01,01)))

Will rheard's formula work for me in the US to adjust for daylight savings time, or is there something else I need to do. I would like to have my time in central standard time and adjust whenever daylight savings hits. Any help would be great. Thanks guys.

 

[rheard]

Hi goduke,
You may be able to adapt my formula to fit your timezone.

begin_date - gets the year of your date field, then sets itself to 1st Jan on this year at 02:00am (UK clocks change at this time in Spring & Fall)

work_date & DLSStart - combine to get the last Sunday in March (US use 2nd Sunday in March)

work_date & DLSEnd - combine to get the last Sunday in October (US use 1st Sunday in November)

Then a simple compare: if your date field is within the range DLSStart to DLSEnd then make the adjustment.

Hope this helps...

 

[goduke]

Hi rheard,

Thanks so much for the reply. I modified the code and hopefully the DLSStart and DLSEnd variables will be correct each year. I tested them and I think they are correct. Maybe you could take a look for me. Thanks again!!

local datetimevar rec_date := dateadd("h",-6,dateadd("s",{call_req.open_date},date(1970,01,01)));
local datetimevar begin_date := datetime(year(rec_date),1,1,2,0,0);
local datetimevar work_date := dateadd("m",3,begin_date);
local datetimevar DLSStart := work_date - dayofweek(work_date) - 13;
work_date := dateadd("m",10,begin_date);
local datetimevar DLSEnd := work_date - dayofweek(work_date) + 8;

//if in DLS Timeframe, add 1 hour
if rec_date in DLSStart to DLSEnd then
local datetimevar result_date := DateAdd("h",+1,rec_date) else
local datetimevar result_date := rec_date;

result_date;

 

[rheard]

Looks OK to me...

- Russ