hi,

is there any function that can give the CST datetime since 1970/01/01 to current datetime as milisecond.

I am using the following:

  
UTCTIME/HYYMDS   = HGETZ(8, 'HYYMDm');
STUTCTIME/HYYMDS = '19700101';
STDATEDIF/I9     = HDIFF(UTCTIME, STUTCTIME, 'DAY', 'I9');
HTIME_DATE/D12   = HTIME(8, UTCTIME, 'D12');MILLISECOND/D20c = ((((STDATEDIF * 24) * 60) * 60)* 1000) + HTIME_DATE;

the output is:
UTCTIME: 2016/07/27 05:45:43
STUTCTIME: 1970/01/01 00:00:00
STDATEDIF: 17009
HTIME_DATE: 20,743,316
MILLISECOND: 1469598343316

it is not looks like CST datetime

thanks,

This message has been edited. Last edited by: <Emily McAllister>,

Unfortunately, HGETC retrieves the Local Date/Time, not UTC. It appears WebFOCUS has no simple way of supplying the UTC Date/Time. The best method of determining UTC is to make a call to a database function. Here is an example for Oracle, it determines the number of milliseconds since 1970-01-01:

SET SQLENGINE = SQLORA
SQL SET DEFAULT_CONNECTION entrpt
-RUN

SQL
WITH T AS (SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) - TO_TIMESTAMP('1970-01-01 00:00:000', 'YYYY-MM-DD HH24:MI:SSFF3') DIFF_DT FROM DUAL)
SELECT (
    EXTRACT(DAY    FROM DIFF_DT) * 24 * 3600000 + 
    EXTRACT(HOUR   FROM DIFF_DT) * 3600000 + 
    EXTRACT(MINUTE FROM DIFF_DT) * 60000 + 
    EXTRACT(SECOND FROM DIFF_DT) * 1000 
) DIF_MS
FROM T;
TABLE ON TABLE HOLD AS HDIFF1
END

TABLE FILE HDIFF1
PRINT
DIF_MS/D15
END
-RUN

The number returned from the SQL call has two decimal places. Perhaps CAST might be able to remove the decimal places.

References:

Current Date / Time in Milliseconds
FocalPoint: Converting a Date to Epoch Time and DATEDIF
FocalPoint: What is date-timestamp Format of START_STAMP and END_STAMP in the BOTLOG Table

quote:

Francis Mariani

Hi Francis Mariani,

what is the equivalent query for MSSQL?

Thanks

Please tell us exactly what you require and what it will be used for. There's some speculation in your other post with the same subject.

CST = Central Standard Time? Are you concerned with Central Daylight Time?

I need the dateTIME from 1970/01/01 to current date time in CST(Central Standard Time) as millisecond.

Using HGETZ to determine the milliseconds from UTC, you can add or subtract the appropriate number of milliseconds to determine CST and CDT.

quote:

Originally posted by FRA-Sarwar:
I need the dateTIME from 1970/01/01 to current date time in CST(Central Standard Time) as millisecond.

Look at your other post with the same subject, I think the answer is there.

quote:

UTCTIME/HYYMDS = HGETZ(8, 'HYYMDm');
STUTCTIME/HYYMDS = '19700101';
STDATEDIF/I9 = HDIFF(UTCTIME, STUTCTIME, 'DAY', 'I9');
HTIME_DATE/D12 = HTIME(8, UTCTIME, 'D12');MILLISECOND/D20c = ((((STDATEDIF * 24) * 60) * 60)* 1000) + HTIME_DATE;

it has been done by my orginal post.