inconsistent date calculations

[dougp]

For a calculation I've been asked to perform in a report, I thought, based on the description, that

Code Select Expand

_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...

Code Select Expand

select datediff(second
, {ts '1970-01-01 00:00:00.000'}
, {ts '2021-07-01 00:00:00.000'})...in SQL Server, I get...

Code Select Expand

1625097600I know Microsoft SQL Server has trouble with dates before 1753, but I have generally found it reliable for recent dates.

But if I run...

Code Select Expand

_unix_timestamp(2021-07-01)...I get...

Code Select Expand

1625122800That's a 7 hour difference.

And if I use data from a package...

Code Select Expand

_unix_timestamp([Current Completion Date])...where [Current Completion Date] is a datetime value of 2021-07-01 00:00:00.000, I get...

Code Select Expand

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,

Code Select Expand

document.writeln((new Date(2021, 7, 1)) - (new Date(1970, 1, 1)));I get yet another answer:

Code Select Expand

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?