For a calculation I've been asked to perform in a report, I thought, based on the description, that _unix_timestamp() function would work well. But I seem to be missing some critical information about either how to use date functions or how to tell time. Any help is appreciated.
In the Functions tab of the Cognos Reporting data item expression editor:
_unix_timestamp ( date_expression )
Returns the number of seconds since 1970-01-01 00:00:00-00 for the given "date_expression".
If I run...
select datediff(second
, {ts '1970-01-01 00:00:00.000'}
, {ts '2021-07-01 00:00:00.000'})...in SQL Server, I get...
1625097600I know Microsoft SQL Server has trouble with dates before 1753 (https://www.timeanddate.com/calendar/julian-gregorian-switch.html), but I have generally found it reliable for recent dates.
But if I run...
_unix_timestamp(2021-07-01)...I get...
1625122800That's a 7 hour difference.
And if I use data from a package...
_unix_timestamp([Current Completion Date])...where [Current Completion Date] is a datetime value of 2021-07-01 00:00:00.000, I get...
1625122800000...which is off by a factor of 1000. So it looks like now it's trying to show the number of milliseconds.
If I use JavaScript to see the difference in milliseconds,
document.writeln((new Date(2021, 7, 1)) - (new Date(1970, 1, 1)));I get yet another answer:
1625094000000That's an 8 hour difference.
What am I missing? Is there a time zone aspect? Are some systems adjusting for Daylight Saving time and others not?