17 Replies Latest reply on Oct 5, 2015 12:55 AM by Eugene Lee

    How can I convert starttime bigint into datetime with a SELECT Command?

    zerenato

      Hi All,

       

      I try to select Event Frames from SQL Server but can't convert bigint to datetime with default time offset. I try

       

           SELECT name, DATEADD(SECOND,starttime/1000 + 8*60*60, '19700101')

       

      but it doesn't work.

       

      Can anybody help.

       

      Thanks,

       

      Jose Renato

        • Re: How can I convert starttime bigint into datetime with a SELECT Command?
          Marcos Vainer Loeff

          Hello Jose Renato,

           

          From my understanding, the type of the StartTime column from the EventFrame table is DateTime which cannot be divided by an integer. I have confirmed this directly in PI SQL Commander and using SQL Server Manager Studio by running the following query on the first option:

           

          SELECT name, starttime
              FROM [DB].[EventFrame].[EventFrame]
          

           

           

          Please run a similar query fixing the database name. What do you receive from the StartTime column:  dates or values?

          • Re: How can I convert starttime bigint into datetime with a SELECT Command?
            Marcos Vainer Loeff

            Hello José Renato,

             

            Since '19700101' and StartTime are both dates, you might want to use DateDiff instead of DateAdd.

             

            The syntax would be: DATEDIFF(SECOND, '19700101', StartTime)

             

            Is this what you are looking for?

              • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                zerenato

                Hi Vainer,

                 

                        I received

                 

                                Msg 8115, Level 16, State 2, Line 2

                                Arithmetic overflow error converting expression to data

                type datetime.

                 

                        it's weird cause the original value of the starttime is

                635241384000000000

                 

                        ...

                 

                 

                 

                Marcos Vainer Loeff <pisquare@osisoft.com>

                01/10/2015 10:55

                Favor responder a

                jive-887622478-2pr-2-162v@mail.osisoft-ext.jiveon.com

                 

                 

                Para

                Jose Souza <zerenato@ons.org.br>

                cc

                 

                Assunto

                Re:  - How can I convert starttime bigint into

                datetime with a SELECT Command?

                 

                 

                 

                 

                 

                 

                 

                PI Square

                 

                How can I convert starttime bigint into datetime with a SELECT Command?

                reply from Marcos Vainer Loeff in PI Developers Club - View the full

                discussion

                 

                Hello José Renato,

                 

                Since '19700101' and StartTime are both dates, you might want to use

                DateDiff instead of DateAdd.

                 

                The syntax would be: DATEDIFF(SECOND, '19700101', StartTime)

                 

                Is this what you are looking for?

                 

                 

                Reply to this message by replying to this email, or go to the message on

                PI Square

                Start a new discussion in PI Developers Club by email or at PI Square

                Following How can I convert starttime bigint into datetime with a SELECT

                Command? in these streams: Inbox

                This email was sent by PI Square because you are a registered user.

                You may unsubscribe instantly from PI Square, or adjust email frequency in

                your email preferences

                • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                  zerenato

                  Here you are,

                   

                   

                   

                   

                   

                  Marcos Vainer Loeff <pisquare@osisoft.com>

                  01/10/2015 10:55

                  Favor responder a

                  jive-887622478-2pr-2-162v@mail.osisoft-ext.jiveon.com

                   

                   

                  Para

                  Jose Souza <zerenato@ons.org.br>

                  cc

                   

                  Assunto

                  Re:  - How can I convert starttime bigint into

                  datetime with a SELECT Command?

                   

                   

                   

                   

                   

                   

                   

                  PI Square

                   

                  How can I convert starttime bigint into datetime with a SELECT Command?

                  reply from Marcos Vainer Loeff in PI Developers Club - View the full

                  discussion

                   

                  Hello José Renato,

                   

                  Since '19700101' and StartTime are both dates, you might want to use

                  DateDiff instead of DateAdd.

                   

                  The syntax would be: DATEDIFF(SECOND, '19700101', StartTime)

                   

                  Is this what you are looking for?

                   

                   

                  Reply to this message by replying to this email, or go to the message on

                  PI Square

                  Start a new discussion in PI Developers Club by email or at PI Square

                  Following How can I convert starttime bigint into datetime with a SELECT

                  Command? in these streams: Inbox

                  This email was sent by PI Square because you are a registered user.

                  You may unsubscribe instantly from PI Square, or adjust email frequency in

                  your email preferences

                    • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                      Marcos Vainer Loeff

                      Hello José Renato,

                       

                      I haven't received the query. Could you please send it again with the explanation of what you are actually trying to do?

                       

                      Thanks in advance!

                        • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                          zerenato

                          Hi Marcos,

                           

                                  I'm trying to find some instances of EventFrame witch PSE

                          interface does not show me, so I'm doing right in the SQL Server database

                          with SELECT statement. In the previous message I sent a screenshot showing

                          what I'm trying to make, it may have been removed from server.

                           

                                  Here is the command:

                           

                                  SELECT name, description, DATEDIFF(SECOND, '19700101',

                          StartTime),DATEDIFF(SECOND, '19700101', EndTime)

                                  FROM AFEventFrame

                                  ORDER BY STARTTIME ASC

                           

                                  Thanks,

                           

                                  Jose Renato.

                           

                           

                           

                           

                          Marcos Vainer Loeff <pisquare@osisoft.com>

                          01/10/2015 16:23

                          Favor responder a

                          jive-887622478-2pr-2-1636@mail.osisoft-ext.jiveon.com

                           

                           

                          Para

                          Jose Souza <zerenato@ons.org.br>

                          cc

                           

                          Assunto

                          Re:  - How can I convert starttime bigint into

                          datetime with a SELECT Command?

                           

                           

                           

                           

                           

                           

                           

                          PI Square

                           

                          How can I convert starttime bigint into datetime with a SELECT Command?

                          reply from Marcos Vainer Loeff in PI Developers Club - View the full

                          discussion

                           

                          Hello José Renato,

                           

                          I haven't received the query. Could you please send it again with the

                          explanation of what you are actually trying to do?

                           

                          Thanks in advance!

                           

                           

                          Reply to this message by replying to this email, or go to the message on

                          PI Square

                          Start a new discussion in PI Developers Club by email or at PI Square

                          Following How can I convert starttime bigint into datetime with a SELECT

                          Command? in these streams: Inbox

                          This email was sent by PI Square because you are a registered user.

                          You may unsubscribe instantly from PI Square, or adjust email frequency in

                          your email preferences

                            • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                              wteo

                              Hi Jose,

                               

                              When querying event frame from PSE interface, PSE actually gets the data from the event frame table in AF SQL Database. Hence, it is quite unlikely that different sets of result returned with the same searching condition using PSE and SQL query. You may probably need to check again your searching criteria for event frames in the PSE.

                                • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                                  zerenato

                                  Hi,

                                   

                                          I found a solution with another values.

                                   

                                          Thanks,

                                   

                                          Jose Renato.

                                   

                                   

                                   

                                  wteo <pisquare@osisoft.com>

                                  01/10/2015 23:44

                                  Favor responder a

                                  jive-1118012367-2pr-2-1653@mail.osisoft-ext.jiveon.com

                                   

                                   

                                  Para

                                  Jose Souza <zerenato@ons.org.br>

                                  cc

                                   

                                  Assunto

                                  Re:  - How can I convert starttime bigint into

                                  datetime with a SELECT Command?

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                  PI Square

                                   

                                  How can I convert starttime bigint into datetime with a SELECT Command?

                                  reply from wteo in PI Developers Club - View the full discussion

                                   

                                  Hi Jose,

                                   

                                  I see that you are trying to convert your StartTime and EndTime from

                                  DateTime format to long values (in seconds) for Unix epoch.

                                   

                                  The query that you use will return all the event frames. Since you are

                                  trying to locate certain instances of event frames, I would suggest you to

                                  include some filtering condition using WHERE clause in your query for

                                  easier searching process. For example, the event frame instances might

                                  fall between 1st Oct 2015 and 2nd Oct 2015. Your query can look something

                                  like:

                                   

                                  SELECT name,

                                                 description,

                                                 DATEDIFF(SECOND, '19700101',StartTime) AS ,

                                                 DATEDIFF(SECOND, '19700101', EndTime) AS

                                  FROM AFEventFrame

                                  WHERE StartTime BETWEEN '01-Oct-2015' AND '02-Oct-2015'

                                  ORDER BY STARTTIME ASC

                                   

                                   

                                  When querying event frame from PSE interface, PSE actually gets the data

                                  from the event frame table in AF SQL Database. Hence, it is quite unlikely

                                  that different sets of result returned with the same searching condition

                                  using PSE and SQL query. You may probably need to check again your

                                  searching criteria for event frames in the PSE.

                                   

                                   

                                  Reply to this message by replying to this email, or go to the message on

                                  PI Square

                                  Start a new discussion in PI Developers Club by email or at PI Square

                                  Following How can I convert starttime bigint into datetime with a SELECT

                                  Command? in these streams: Inbox

                                  This email was sent by PI Square because you are a registered user.

                                  You may unsubscribe instantly from PI Square, or adjust email frequency in

                                  your email preferences

                              • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                                zerenato

                                Hi Marcos,

                                 

                                        I have to remove all Event Frames from elements of an AF tree and

                                generate it again, but I only have a backfill option tool in PSE. However

                                this tree is very and its impossible to click an each element to remove

                                the EventFrames and then trigger the backfill option. Is there a more

                                efficient way to do this?

                                 

                                        Jose Renato

                                 

                                 

                                 

                                Marcos Vainer Loeff <pisquare@osisoft.com>

                                01/10/2015 16:23

                                Favor responder a

                                jive-887622478-2pr-2-1636@mail.osisoft-ext.jiveon.com

                                 

                                 

                                Para

                                Jose Souza <zerenato@ons.org.br>

                                cc

                                 

                                Assunto

                                Re:  - How can I convert starttime bigint into

                                datetime with a SELECT Command?

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                PI Square

                                 

                                How can I convert starttime bigint into datetime with a SELECT Command?

                                reply from Marcos Vainer Loeff in PI Developers Club - View the full

                                discussion

                                 

                                Hello José Renato,

                                 

                                I haven't received the query. Could you please send it again with the

                                explanation of what you are actually trying to do?

                                 

                                Thanks in advance!

                                 

                                 

                                Reply to this message by replying to this email, or go to the message on

                                PI Square

                                Start a new discussion in PI Developers Club by email or at PI Square

                                Following How can I convert starttime bigint into datetime with a SELECT

                                Command? in these streams: Inbox

                                This email was sent by PI Square because you are a registered user.

                                You may unsubscribe instantly from PI Square, or adjust email frequency in

                                your email preferences

                                  • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                                    Marcos Vainer Loeff

                                    Hello Jose Renato,

                                     

                                    Please refer to the code snippet below, which uses PI AF SDK on a C# project:

                                     

                                                PISystem piSystem = new PISystems()["AFSERVERNAME"];
                                                AFDatabase afDatabase = piSystem.Databases["DBNAME"];
                                    
                                    
                                                AFNamedCollectionList<AFEventFrame> eventFrameList = AFEventFrame.FindEventFrames(afDatabase, null, AFSearchMode.Overlapped, "*-100d", "*",
                                        "", "", null, null, null, true, AFSortField.StartTime, AFSortOrder.Ascending, 0, 100);
                                    
                                    
                                    
                                    
                                                foreach (AFEventFrame eventFrame in eventFrameList)
                                                {
                                                    Console.WriteLine(eventFrame);
                                                    eventFrame.Delete();
                                                }          
                                              
                                                afDatabase.CheckIn(AFCheckedOutMode.ObjectsCheckedOutThisSession);
                                                Console.WriteLine("Finished");
                                                Console.ReadKey();
                                    

                                     

                                    I am using the FindEventFrames static method to get all event frames whose start time is more recent than 100 days ago. Then I iterate through the collection and for each item I call the Delete() method. Finally, I just need to execute the CheckIn() method n in order to save the changes.

                                     

                                    You need to make sure that the event frames within the collection are the ones you want to delete. There are other static methods to find event frames. According to your AFTree there might be better options. This is just a code snippet for you to get started!

                                     

                                    Hope it helps!

                                    • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                                      Eugene Lee

                                      Hi Jose,

                                       

                                      If I'm not wrong, the backfill will automatically delete any existing event frames.

                                       

                                      The event-frame-generation analysis automatically deletes any existing event frames during that time period.

                                       

                                      This can save you the effort of deleting it yourself.

                                       

                                      https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v5/GUID-AE048C0A-33B0-42B0-BEA7-1D51B21252E0

                              • Re: How can I convert starttime bigint into datetime with a SELECT Command?
                                Eugene Lee

                                Hi Jose,

                                 

                                You can do this. I suppose you are located in GMT+8 like me.

                                 

                                 

                                I would also like to discourage your use of the PIFD database and instead use PI OLEDB Enterprise if possible to get your data as Marcos has pointed out.