2 Replies Latest reply on Dec 21, 2011 2:12 PM by gregcaine

    Get Hour from mySQL TimeStamp field



      Get Hour from mySQL TimeStamp field


      I have an ODBC connection from FileMaker Pro Advanced 11 to a table in mysQL 5.1 that has a timestamp field. From FileMaker, the time in the timestamp field displays 12:00:00. AN ODBC connection from ACCESS 2007 correctly displays the timestamp.

      I need to create a calculated field for records created after 11:00PM. Using the HOUR function and other attempts have been unsuccessful.

      Can someone suggest a way to capture the timestamp so I can parse by the hour?

        • 1. Re: Get Hour from mySQL TimeStamp field

          If you convert the value into a number what do you see?

          Try this for several different time values with the same date and compare these to the number values of FileMaker TimeStamp fields for the same date and time. FileMaker timestamp fields meaure date, time in terms of an arbitrary number of seconds and MySQL likely does too, but from a different "0 day and hour" than FileMaker. You can probably convert the MySQL timestamp into a FileMaker timestamp by adding or subtracting a certain number of seconds from/to the MySQL value.

          To use a FileMaker TimeStamp field in a calculation to show the number value, define a calculation field that simply refers to the timestamp field by name and select number as its return type.

          • 2. Re: Get Hour from mySQL TimeStamp field

            I bypassed FileMaker and choose to use ACCESS as a front-end using the expression below. Before I investigate the hour conversion in FileMaker using the answer above, is there a more efficient solution as of December 2011? Thanks. 

            Var_Date: IIf(Format([DateField],"hh")<23,Format([DateField],"mm-dd-yyyy"),Format(DateAdd("d",1,[DateField]),"mm-dd-yyyy"))