Hi, 

The reason that you are getting "Wrong value", is because you are multiplying with a Long type and the result is bigger than long. Just try this little code:

longlong ll_return_milli
long ll_days, ll_temp
longlong ll_Ldays, ll_Ltemp

ll_days = 18661
ll_Ldays = 18661

ll_temp = 24 * 60 * 60 * 1000 	// 86400000
ll_return_milli = ll_days * ll_temp // 1697664000 	-> Wrong value
ll_return_milli = LongLong(ll_days, 0) * ll_temp // 1612310400000 	-> Correct value when using LongLong()

ll_Ltemp = 24 * 60 * 60 * 1000 	//	86400000
ll_return_milli = ll_Ldays * ll_Ltemp // 1612310400000 	-> Correct value when using a LongLong datatype

Now I know that mathematical rules exist, but I don't really see why the result is wrong when you multiply with Long value and the end result is being assigned correctly to a LongLong, but it does fail. (I think powerbuilder decides that the result should fit into a long, before doing the assigment to the ll_return_milli variable).
Maybe this could be a bug?

Anyway to solve your problem, simply declare the ll_days variable as a LongLong variable (instead of long), and then your calculation of: 

ll_return_milli = ll_days * 24 * 60 * 60 * 1000

, should then work correclty.

regards

Hello,

I could get UTC time from database by returning the value as a string.  Now a small issue in arriving the milliseconds that we could not multiply the 1000 in a single row, but by multiplying it three times (by 10 each), we could get the correct value.

**************************************************
LongLong                  ll_return_milli
Long                         ll_days, ll_seconds
Long                         ll_hours, ll_min, ll_sec, ll_micro
Datetime                   ld_basedatetime
Date                         ld_system_date

ld_basedatetime = Datetime('1970/01/01 00:00:00')

Select today_now
into :as_system_datetime
From (Select Cast(now() at time zone 'utc' as varchar) as today_now) a;

ld_system_date = Date(Mid(as_system_datetime, 1, 10))
ll_hours = Long(Mid(as_system_datetime, 12, 2))
ll_min = Long(Mid(as_system_datetime, 15,2))
ll_sec = Long(Mid(as_system_datetime, 18,2))
ll_micro = Long(Mid(as_system_datetime, 21))

ll_days = DaysAfter(Date(ld_basedatetime), ld_system_date)

// Multiplying all in one single line gives wrong value
// So the last 1000 is multiplied in 3 rows below
//ll_return_milli = ll_days * 24 * 60 * 60 * 1000                         -> Wrong value

ll_return_milli = (ll_days * (24 * 60 * 60))
ll_return_milli = ll_return_milli * 10
ll_return_milli = ll_return_milli * 10
ll_return_milli = ll_return_milli * 10                                            -> Correct value

ll_return_milli += (ll_hours * 60 * 60 * 1000)
ll_return_milli += (ll_min * 60 * 1000)
ll_return_milli += (ll_sec * 1000)
ll_return_milli += ll_micro

Return ll_return_milli
*************************************************

Don't know why (ll_days * 24 * 60 * 60 * 1000) doesn't give the correct result, when ll_days = 18661.  {haven't checked with lower values}

But multiplying 1000 in three times (each time by 10), gives the correct result.

Happiness Always
BKR Sivaprakash

Greetings, Sivaprakash - 

While I am not able to help with the database-related portion of your questions, I may have something to offer for the elapsed time or time differential parts.

As part of a demo application I'm currently developing, I needed a way to perform time-based calculations. I wrote a non-visual object, n_timecalc, that contains the following functions:

All of the calculations are made to the full resolution of the PB Time datatype, which is microseconds. I attaching a zip file that contains the exported source of this NVO here.

Please note all of these functions operate on Time values only, not Datetime. Even so, I hope it may help meet your needs.

Regards, John

Since you probably are using ODBC to connect, which dbparm specifications do you have for 'datetime'?

Syntax The syntax you use to specify the DateTime differs slightly depending on the
database.
The Database Profile Setup dialog box inserts special characters (quotes and
backslashes) where needed, so you can specify just the DateTime format.
In code, you must use the following syntax:
JDBC and ODBC syntax PowerBuilder parses the backslash followed by
two single quotes (\'') as a single quote when it builds the SQL UPDATE
statement.
DateTime=' \''DateTime_format\'' '

JDBC and ODBC syntax Type a single quote, followed by
one space, followed by a backslash, followed by two single
quotes. There is no space between the two single quotes and the
beginning of the DateTime format.

Example 1 (JDBC, ODBC, and OLE DB syntax) To specify that
PowerBuilder should use this format for the DateTime datatype when it builds
the SQL UPDATE statement:
• Database profile Type the following in the DateTime Format box on the
Syntax page in the Database Profile Setup dialog box:
m/d/yy h:mm am/pm
• Application Type the following in code:
SQLCA.DBParm="DateTime=' ''m/d/yy h:mm am/pm\'' '"
\'' '
'''' '
JDBC and ODBC syntax Type a backslash, followed by two
single quotes, followed by one space, followed by a single
quote. There is no space between the end of the date format and
the backslash.

ODBC If no value is specified for the DateTime database parameter,
PowerBuilder looks for a DateTime format in the section for your
ODBC driver in the PBODBxxx.INI initialization file. If no DateTime
format is found in the initialization file, PowerBuilder uses the ODBC
DateTime format escape sequence.

Have you adapted the pbodb.ini file?

I haven't used postgresql yet, but on this link http://codeverge.com/sybase.powerbuilder.connection/pb-with-postgresql/903662 they mention something about having to adapt the pbodb.ini or pbodbxxxx.ini file. (depending on which version of pb your are).

I'm not sure if that could be maybe important.

just an idea.

Hi BKR,

So you're close, just a couple tweaks and you should have it.

Issue 1

Milliseconds are tracked/ included internally but they do not show unless "requested" and so how do you request them? Use the string function and format it to include the milliseconds with "fff" (as you did with your database approach) and save it to a variable for later use. This will work either on a DateTime value or a Time value such as Now().

Once you have the milliseconds included as a string you can then strip them out converting them to decimal values and perform necessary calculations.

Issue 2

No, as far as I have understood SecondsAfter() to work it does not take into account milliseconds (other than rounding up or down to the nearest second).

Additional Thoughts

Be careful in calculating your number of days via DaysAfter() as it includes full days (assumes midnight to midnight). For example, if your time is 7:35:48:259 am then it's 1 less day plus 7 hours, 35 minutes, 48 seconds and 259 milliseconds.

HTH...regards,

Mark

Hello,

Using PB 2017R3, Postgresql 11

Trying to arrive the milliseconds in Pb through the following function 
**********************************************************
Long ll_return_milli, ll_days, ll_seconds
Datetime ld_basedatetime

ld_basedatetime = Datetime('1970/01/01 00:00:00')
ll_days = Daysafter(Date(ld_basedatetime), Date(as_system_datetime))
ll_seconds = SecondsAfter(Time(ld_basedatetime), Time(as_system_datetime))

ll_return_milli = (ll_days * 24 * 60 * 60 * 1000) + (ll_seconds * 1000)
Return ll_return_milli
*****************************************************************

Issue is with milliseconds.   

Issue 1

I could not read the milliseconds value either from the system datetime or from the database.   Don't know whether the datetime variable is limited upto seconds only or not ....

I tried with to assign (read) values, to pass to the function.

1.  by reading system date time
     ldt_curr_sys_date = DateTime(Today(), Now())

2.  by querying the db
     Select today_now   into :ldt_curr_db_date  from (Select Now() as today_now ) a;

Messagebox('Result', 'Date from System : ' + String(ldt_curr_sys_date, 'dd/MM/yyyy hh:mm:ss.ffff') + '~r~n' + 'Date from DB : ' + String(ldt_curr_db_date, 'dd/MM/yyyy hh:mm:ss.ffff')

I didn't get the milliseconds part.   Why ?  Does the variable don't get the milliseconds part ?

When I query the database directly, I get the milliseconds part, with time zone.   Why doesn't it get retrieved / stored / displayed in powerbuilder?

Issue 2

SecondsAfter PB Function.  Not sure whether it takes milliseconds also into account while arriving the seconds.

Any insight thoughts ?

Bang on Miguel, as usual ;) Always look forward to your responses (and suggestions, questions)!

In addition to what Miguel suggested it's relatively simple to calculate if you wanted to create a function on your own as there are 86,400 seconds in a day and so 31,536,000 seconds in a non-leap year and 31,622,400 seconds in a leap year. Not too difficult to figure out how many leap years between two dates.

That being said, PowerBuilder's built in function DaysAfter() providing the number of days between two dates multiplied by number of seconds in a day and then adding (or subtracting) the time to account for the time of day the process is run should get you there.

Of course all the seconds referenced above need to be converted to milliseconds.

ALL that being said, lol, if you didn't want to build a function yourself, you could simply call a web service/ API (https://unixtime.co.za/) to do it for you.

So in summary you could utilize a database function, create a function yourself in PowerBuilder, call an external function or call upon an API.

HTH

Regards,

Mark

Another thing you can do (easier than calling a Java function) is to write an assembly in C#. It easy now to call a C# function from powerbuilder.

This is what you could do in C#:

DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()

Java also has this function. You could write something in Java and call it from powerbuilder.

Or if you are using Oracle then have a look at this link:

https://stackoverflow.com/questions/2824710/oracle-equaivalent-of-java-system-currenttimemillis

Or,

You can calculate it yourself. (on the link to Oracle it says how it says it's the number of milliseconds passed since somewhere in 1970, I think).

just some ideas.