Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conversions between UNIX time and normal time

Status
Not open for further replies.

Rbp1226

Technical User
Feb 22, 2008
21
US
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!

 
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)
 
So using Lyle's explanation, I think you can use:

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

-LB
 
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)
 
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...

Code:
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

 
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
 
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
 
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.
 
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...
 
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top