CODEWITHSUNDEEP
mysqlsas
Robert Penridge
Robert Penridge

Reputation: 8513

Converting SAS datetime to UNIX timestamp in SAS

I'm trying to convert a SAS datetime to a UNIX timestamp but am getting an 8 hour difference in my results. I think it's somehow related to a timezone issue but I can't figure out how.

To start with I ran the following statement in mySQL to get the desired result.

Query:

SELECT UNIX_TIMESTAMP('2017-01-01 00:00:00') AS wanted_unix_timestamp

Result:

UNIX_TIMESTAMP('2017-01-01 00:00:00')  
---------------------------------------
                             1483221600

I then tried to write SAS code to convert a SAS datetime into that number but it seems to be 8 hours off:

%let wanted_unix_timestamp = 1483221600;

%let sas_epoch = '01jan1960:00:00:00'dt;
%let unix_epoch = '01jan1970:00:00:00'dt;
%let unix_timestamp_offset = %sysfunc(intck(seconds,&sas_epoch,&unix_epoch)); 

%let sas_datetime = %sysfunc(dhms(%sysfunc(mdy(1,1,2017)),0,0,0));

%let unix_timestamp = %eval(&sas_datetime - &unix_timestamp_offset);

%put &=sas_datetime;
%put &=unix_timestamp_offset;
%put &=unix_timestamp;
%put %eval(&wanted_unix_timestamp - &unix_timestamp);

Result:

SAS_DATETIME=1798848000
UNIX_TIMESTAMP_OFFSET=315619200
UNIX_TIMESTAMP=1483228800
-7200

Based on some google searches my offset seems to be correct. I've also tried setting the session timezone in mySQL to different timezones to see if that makes a difference by using SET SESSION time_zone = 'US/Pacific'; and SET SESSION time_zone = 'Europe/Helsinki'; but they make no difference to the output.

Edit: I'm using SAS 9.4 so if there's an easier way to go about this then I'd happily take that as the answer too.

Upvotes: 2

Views: 2424

Answers (1)

Joe
Joe

Reputation: 63424

You're almost certainly being affected by the timezone, because UNIX_TIMESTAMP returns the current time as seconds from 1970-01-01 00:00:00 UTC. Hence the 8 hour difference (being in the Pacific timezone).

Per mySQL reference on session time zones, setting the session timezone does not affect UTC_TIMESTAMP, and while it doesn't mention UNIX_TIMESTAMP directly, it presumably has the same non-effect there.

Upvotes: 1

Related Questions