1. Sivaprakash BKR
  2. PowerBuilder
  3. Saturday, 30 January 2021 13:35 PM UTC

Hello,

Need to arrive the milliseconds since since the Unix epoch (January 1, 1970 00:00:00 UTC) something similar to  System.currentTimeMillis() available in Android / Unix.  

Any function or code snippet available to arrive this ?

Happiness Always
BKR Sivaprakash

 

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 3 February 2021 07:59 AM UTC
  2. PowerBuilder
  3. # 1

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

 

Comment
  1. Sivaprakash BKR
  2. Wednesday, 3 February 2021 08:21 AM UTC
Thanks Miguel,

It works. Don't know how it works correctly when adding converted hours, min and sec values. All are declared as long only.
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 3 February 2021 08:28 AM UTC
YW, you mean "how these lines work" while ll_hours, etc. are long?

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

That's because the result of for example the first one "(ll_hours * 60 * 60 * 1000)" does still fit in a long. The result of the calculation that failed when multiplying, does not fit in a long, so you'd get a wrong value.

Happy you solved it!

regards
  1. Helpful
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Wednesday, 3 February 2021 07:02 AM UTC
  2. PowerBuilder
  3. # 2

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

 

Comment
  1. Miguel Leeuwe
  2. Wednesday, 3 February 2021 07:51 AM UTC
The reason that you are getting "Wrong value", is because you are multiplying with a Long type and the result is bigger than long. See my last answer.
  1. Helpful
  1. Sivaprakash BKR
  2. Wednesday, 3 February 2021 08:23 AM UTC
Modified the ll_days variable to longlong. It works. Thanks Miguel.
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 2 February 2021 20:31 PM UTC
  2. PowerBuilder
  3. # 3

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

Attachments (1)
Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 2 February 2021 16:27 PM UTC
  2. PowerBuilder
  3. # 4

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.

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 2 February 2021 16:07 PM UTC
  2. PowerBuilder
  3. # 5

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.

Comment
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Tuesday, 2 February 2021 14:38 PM UTC
  2. PowerBuilder
  3. # 6

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

Comment
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 2 February 2021 05:48 AM UTC
  2. PowerBuilder
  3. # 7

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 ?

 

 

Comment
  1. Miguel Leeuwe
  2. Tuesday, 2 February 2021 16:18 PM UTC
About issue 1.2: If you want to get milliseconds from the DB using a select you have to include a format like "....:MS".

See https://www.postgresql.org/docs/10/functions-formatting.html

  1. Helpful
  1. Mark Goldsmith
  2. Tuesday, 2 February 2021 19:27 PM UTC
I too am not very familiar with PostgreSQL but I know you can execute something like SELECT UNIX_TIMESTAMP(sysdate(3)) to obtain what you're looking for from a MySQL database.

I suspect PostgreSQL has similar epoch functions but don't know for certain.

Mark
  1. Helpful
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Saturday, 30 January 2021 16:31 PM UTC
  2. PowerBuilder
  3. # 8

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

Comment
  1. Miguel Leeuwe
  2. Saturday, 30 January 2021 16:46 PM UTC
Thank you Mark .. and all I did ... simply Google it!
  1. Helpful
  1. Sivaprakash BKR
  2. Tuesday, 2 February 2021 04:27 AM UTC
Thanks Mark. Will do.

  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 30 January 2021 15:37 PM UTC
  2. PowerBuilder
  3. # 9

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()

 

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 30 January 2021 15:13 PM UTC
  2. PowerBuilder
  3. # 10

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.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.