• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Does anyone know how to manipulate date values to allow for varying timezones?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Does anyone know how to manipulate date values to allow for varying timezones?

  1. Issue
0
Votes
Undo
  1. Jim Means
    Jim Means
  2. PowerBuilder
  3. Friday, 1 June 2018 16:59 PM UTC

Hi All,

One of our PB apps is used in multiple timezones, with users in those differing timezones hitting the same Oracle table rows, based on their role.  Because of that, the varying timestamps in our tables are off, making it rough to report on productivity.

I'm just getting ready to move to PB 2017.  Was wondering if anyone happens to know how to recalculate a date in PB (like an Oracle SYSDATE - n).  I never could find it in our old PB 11.2 help.  I do have a way to tell where the user is at the time of update, based on their security profile.

Thanks!
Jim

  1. DATE
Cannot activate our new license for Powerbuilder
Datawindows are consuming too much windows resourc...
Accepted Answer
Aubrey Eichel
Aubrey Eichel Accepted Answer Pending Moderation
  1. Friday, 1 June 2018 22:40 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Jim.  I don't know if you have the power to change the database, but if you do, you may want to let Oracle do the heavy lifting.  If the timezone is stored with the timestamps, it should make any calculations that are necessary some what less complex.  Although our application does not make use of this as it is only implemented locally, use of the timezone may be helpful for you.  Here is a link to an Oracle 11g document describing the use of timezones: https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004

Good luck,
Aubrey

 

 

Comment
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Monday, 4 June 2018 12:27 PM UTC
I would have used Oracle to do this work. It has this functionality built in.

  1. Helpful
There are no comments made yet.
Responses (4)
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 5 June 2018 12:25 PM UTC
  2. PowerBuilder
  3. # 1

Storing the datetime in UTC value is the go.  Here are the code to convert a given date time to UTC and a UTC date time to powerbuilder datetime values.

--Create global external function
FUNCTION long uuidCreate(ref s_uuid astr_uuid) LIBRARY "Rpcrt4.dll"  ALIAS FOR "UuidCreate"
FUNCTION ulong GetTimeZoneInformation (ref TIME_ZONE_INFORMATION lpTimeZoneInformation) Library "kernel32"
FUNCTION boolean SystemTimeToTzSpecificLocalTime(TIME_ZONE_INFORMATION lpTimeZone, SYSTEMTIME lpUniversalTime, ref SYSTEMTIME lpLocalTime ) Library "kernel32"
FUNCTION boolean TzSpecificLocalTimeToSystemTime(TIME_ZONE_INFORMATION lpTimeZone, SYSTEMTIME lpLocalTime, ref SYSTEMTIME lpUniversalTime ) Library "kernel32"

_______________________________________________________________________________________________

-- function to convert passed date time to UTC
function f_convert_sysdatetime_to_UTC (as_systemdate datetime)
 

TIME_ZONE_INFORMATION tzi
SYSTEMTIME utc
SYSTEMTIME pst
ulong rc
boolean rc2
 
 
//Get's local time zone
rc = GetTimeZoneInformation(tzi)
 
//Convert PowerBuilder datetime to SYSTEMTIME for UTC
UTC.wYear = Year(Date(as_systemdate))
UTC.wMonth = Month(Date(as_systemdate))
UTC.wDay = Day(Date(as_systemdate))
UTC.wHour = Hour(Time(as_systemdate))
UTC.wMinute = Minute(Time(as_systemdate))
UTC.wSecond = Second(Time(as_systemdate))
UTC.wMilliseconds = 0
 
// Convert to UTC
rc2 = TzSpecificLocalTimeToSystemTime(tzi, UTC, pst)
 
//Convert SYSTEMTIME to PowerBuilder datetime
Return DateTime ( Date ( pst.wYear, pst.wMonth, pst.wDay ),   Time ( pst.wHour, pst.wMinute, pst.wSecond ) )
______________________________________________________________________________________________________________________
 
--function to convert UTC datetime to systemdate time
function f_convert_utc_to_systemdatetime (as_utc datetime)
 
TIME_ZONE_INFORMATION tzi
SYSTEMTIME utc
SYSTEMTIME pst
ulong rc
boolean rc2
 
 
//Get's local time zone
rc = GetTimeZoneInformation(tzi)
 
//Convert PowerBuilder datetime to SYSTEMTIME for UTC
UTC.wYear = Year(Date(as_utc))
UTC.wMonth = Month(Date(as_utc))
UTC.wDay = Day(Date(as_utc))
UTC.wHour = Hour(Time(as_utc))
UTC.wMinute = Minute(Time(as_utc))
UTC.wSecond = Second(Time(as_utc))
UTC.wMilliseconds = 0
 
//Convert to local time zone
rc2 = SystemTimeToTzSpecificLocalTime(tzi, UTC, pst)
 
//Convert SYSTEMTIME to PowerBuilder datetime
Return DateTime ( Date ( pst.wYear, pst.wMonth, pst.wDay ),   Time ( pst.wHour, pst.wMinute, pst.wSecond ) )

____________________________________________________________________________________________________________________

Hope this helps.

 

 

Comment
Jim Means
  1. Jim Means
  2. Tuesday, 5 June 2018 14:12 PM UTC
Thanks to everyone for their suggestions and code samples!  I'll give them a try.

  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Saturday, 2 June 2018 10:10 AM UTC
  2. PowerBuilder
  3. # 2

Hi Jim,

Use UTC timestamps to get comparable timestamps. Unfortunately, PowerBuilder does not have TodayUTC( ), nor NowUTC( ).

Use DB to obtain current UTC timestamp if you need to sort very precisely events/data coming multiple machines.

  • PL/SQL :: SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;
  • Transact-SQL (MS and ASE) :: SELECT GetUTCDate( )
  • SQL Anywhere :: SELECT CURRENT UTC TIMESTAMP

Use Win API to obtain current UTC timestamp and convert to/from local time if you just need "comparable" time though exact sorting isn't needed. I have an NVO wrapping time functionality. Just remember individual machines are easily several minutes apart on their clocks - even when they synchronize via the internet.

Back in 1988-'89 I worked on project where we logged all status alerts coming from all technical systems used to operate/control Danish civilian airspace. To make sure we could identify event sequences correctly, we would UTC stamp all alerts when they arrived at our central data store. Languages: Turbo Pascal for DOS PC clients; DG-Pascal for server processes on Data General mini computers.

HTH /Michael

Comment
Michael Kramer
  1. Michael Kramer
  2. Saturday, 2 June 2018 10:44 AM UTC
Hi Jim, follow-up regarding calculating on timestamps. I extended my time NVO to also do RelativeDate/Time and calculate time intervals for DateTime values. When calculating on local time, my NVO converts to UTC; calculates; and maps back to local time. Otherwise entering and leaving daylight savings time will screw up the calculations.



 



You can use Oracle to retrieve in UTC if data is stored including time zone offset. Same as for getting timestamp:



SELECT ApprovalStamp AS TIME ZONE 'UTC', ApprovedBy FROM ImportantApprovals WHERE ApprovalSeqNo = 123456;

  1. Helpful
There are no comments made yet.
Matthew Balent
Matthew Balent Accepted Answer Pending Moderation
  1. Friday, 1 June 2018 17:25 PM UTC
  2. PowerBuilder
  3. # 3

It looks like you are looking for RelativeDate and RelativeTime methods.  DaysAfter and SecondsAfter may be of use as well.

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.