10 Replies Latest reply on May 20, 2016 9:05 AM by beverly

    Extracting Time from a Timestamp

    RPeters

      Hello again Filemaker friends!

       

      I have a Timestamp field which I use most often, but sometimes I'd really like to have the fields separated.  I have a separate date field with the calculation: GetAsDate ( Timestamp )  It works great, but I tried a similar thing in a Time field and it comes up empty.

       

      I have a Time field (field type is set to Time) with the calculation GetAsTime ( Timestamp ) and it doesn't produce any results...

       

      Any ideas???

        • 1. Re: Extracting Time from a Timestamp
          beverly

          Did you try GetAsTime(timestampField)?

          Tested in Data Viewer:

          Let (

          [ ts =Get ( CurrentHostTimestamp )

          ; t = GetAsTime ( ts )

           

          ]; "TS: " & ts & "¶t: " & t

          )

           

          result:

          TS: 5/19/2016 1:05:23 PM

          t: 1:05:23 PM

           

          beverly

          • 2. Re: Extracting Time from a Timestamp
            user19752

            Did you enter the timestamp value after creating time field?

             

            "Seconds" part can be empty if it is 0, (00:00, not 00:00:00) but whole field?

            • 3. Re: Extracting Time from a Timestamp
              beverly

              Right! OP may need to test for 0 or empty!

              beverly

              • 4. Re: Extracting Time from a Timestamp
                RPeters

                Thanks for your help everyone!

                 

                In my Timestamp field, I had entered this:

                2016/08/06 7:00pm

                 

                I tried adding seconds onto my Timestamp field so it read like this:

                2016/08/06 7:00:00pm

                 

                And now it works!  The time field which has this calculation

                GetAsTime ( Timestamp )

                extracts the time, just as I'd hoped it would!

                 

                Although, I don't quite understand why it works now... Anyone have a sec to explain it? I'm still pretty new with Filemaker, but I'm gettin' there!

                • 5. Re: Extracting Time from a Timestamp
                  beverly

                  I don't understand why adding seconds should matter.

                  In data viewer:

                  GetAsTime("1/2/2016 7:00 pm")

                  returns:

                       7:00 PM

                  I even removed the space and got the correct results.

                   

                   

                  beverly

                  • 6. Re: Extracting Time from a Timestamp
                    user19752

                    Agreed.

                    I think seconds is not the matter, re-entering value make re-calculate on newly created auto-enter field.

                    • 7. Re: Extracting Time from a Timestamp
                      beverly

                      Ah, yes, that may be the "key" recalculating the auto-enter because of the change.

                      HELPFUL!

                      beverly

                      • 8. Re: Extracting Time from a Timestamp
                        RPeters

                        That's what I wasn't understanding either! So strange!  I tried mine again (like you tested as well) just to see and now mine is working correctly.

                         

                        As I was going through and checking all my events one thing that I noticed was that some fields had data in them before I created the calculation... I wonder if that messes everything up??  (At one point I was typing into the timestamp field as well as the time field.)  Not all of them had data though... could that have maybe been the case?

                        • 9. Re: Extracting Time from a Timestamp
                          kamal1234

                          I worked on this problem couple of days back. I found one custom function online. I modified it to make it suitable to my purpose. You can further modify it or use it as it is. I hope it helps.

                           

                          It take two arguments

                          1) TheTimeStamp

                          2) FormatString

                          For example

                           

                          date.format ( Get( CurrentTimestamp ); "%o %T" ) will produce 2016-05-20 11:43:00

                           

                           

                           

                          Let(

                            [

                              $_Timestamp = GetAsTimestamp( TheTimestamp );

                              $_Month = Month( $_Timestamp );

                              $_Day = Day( $_Timestamp );

                              $_Year = Year( $_Timestamp );

                              $_Hour = Hour( $_Timestamp );

                              $_LowHour = Mod( $_Hour; 12 );

                              $_Minute = Minute( $_Timestamp );

                              $_Second = Seconds( $_Timestamp );

                           

                           

                          //  D - Date in mm/dd/yy format (06/14/06)

                              $_D = Right( "0" & $_Month; 2 ) & "/" &

                                Right( "0" & $_Day; 2 ) & "/" &

                                Right( $_Year; 2 );

                          //  DDD - Date in yyyy-mm-dd format (2016-09-23)

                              $_DDD = Right( $_Year; 4 ) & "-" &

                            Right( "0" & $_Month; 2 ) & "-" &

                                Right( "0" & $_Day; 2 ) ;

                           

                           

                          //  x - Date in standard format for locale, for this solution just the same as D

                              $_xx = $_D;

                           

                           

                          //  C - Century (20 for 2006)

                              $_C = Left( $_Year; 2 );

                          //  Y - Year in 4-digit format (2006)

                              $_Y = $_Year;

                          //  y - Year in 2-digit format (06)

                              $_yy = Right( $_Year; 2 );

                          //  G - Same as Y

                              $_G = $_Y;

                          //  g - Same as y

                              $_g = $_yy;

                           

                           

                          //  b - Month name - abbreviated (Jan)

                              $_bb = Left( MonthName( $_Timestamp ); 3 );

                          //  B - Month name - full (January)

                              $_B = MonthName( $_Timestamp );

                          //  h - Same as b

                              $_hh = $_bb;

                          //  m - Month number (06)

                              $_mm = Right( "0" & $_Month; 2 );

                           

                           

                          //  W - Week of the year (00-52)

                              $_W = WeekOfYear( $_Timestamp ) - 1;

                          //  V - Week of the year (01-51)

                              $_V = $_W + 1;

                          //  U - Same as W

                              $_U = $_W;

                           

                           

                          //  A - Day of the week - full name (Monday)

                              $_A = DayName( $_Timestamp );

                          //  a - Day of the week - abbreviated name (Mon)

                              $_aa = Left( $_A; 3 );

                          //  u - Day of the week - number (Sunday = 1, although date tool has Monday = 1)

                              $_uu = DayOfWeek( $_Timestamp );

                          //  d - Day of the month - 2 digits (05)

                              $_dd = Right( "0" & $_Day; 2 );

                          //  e - Day of the month - digit preceded by a space ( 5)

                              $_ee = " " & $_Day;

                          //  j - Day of the year - (1-366)

                              $_jj = DayOfYear( $_Timestamp );

                          //  w - Same as u

                              $_ww = $_uu;

                           

                           

                          //  p - AM or PM

                              $_pp = Case( $_Hour < 12; "AM"; "PM" );

                          //  r - Time in 12-hour format (09:15:36 AM)

                              $_rr = Right( "0" & $_LowHour; 2 ) & ":" &

                                Right( "0" & $_Minute; 2 ) & ":" &

                                Right( "0" & $_Second; 2 ) & " " &

                                $_pp;

                          //  R - Time in 24-hour format - no seconds (17:45)

                              $_R = Right( "0" & $_Hour; 2 ) & ":"  &

                                Right( "0" & $_Minute; 2 );

                          //  T - Time in 24-hour format (17:45:52)

                              $_T = $_R & ":" & Right( "0" & $_Second; 2 );

                          //  X - Same as T

                              $_X = $_T;

                          //  Z - Time zone offset from UTC (-07), NOT SUPPORTED

                           

                           

                          //  H - Hour in 24-hour format (17)

                              $_H = Right( "0" & $_Hour; 2 );

                          //  I - (upper case i) Hour in 12-hour format (05)

                              $_I = Right( "0" & $_LowHour; 2 );

                          //  k - Same as H

                              $_k = $_H;

                          //  l - (lower case L) Same as I

                              $_ll = $_I;

                           

                           

                          //  M - Minutes (35)

                              $_M = Right( "0" & $_Minute; 2 );

                          //  S - Seconds (05)

                              $_S = Right( "0" & $_Second; 2 );

                          //  s - Seconds elapsed since 1/1/1970

                              $_ss = GetAsNumber( $_Timestamp - Timestamp( Date( 1; 1; 1970 ); Time( 0; 0; 0 ) ) )

                            ];

                           

                           

                            Substitute( FormatString;

                              [ "%%"; "%"];

                              [ "%D"; $_D ];

                              [ "%x"; $_xx ];

                              [ "%C"; $_C ];

                              [ "%Y"; $_Y ];

                              [ "%y"; $_yy ];

                              [ "%G"; $_G ];

                              [ "%g"; $_gg ];

                              [ "%b"; $_bb ];

                              [ "%B"; $_B ];

                              [ "%h"; $_hh ];

                              [ "%m"; $_mm ];

                              [ "%W"; $_W ];

                              [ "%V"; $_V ];

                              [ "%U"; $_U ];

                              [ "%a"; $_aa ];

                              [ "%A"; $_A ];

                              [ "%u"; $_uu ];

                              [ "%d"; $_dd ];

                              [ "%e"; $_ee ];

                              [ "%j"; $_jj ];

                              [ "%w"; $_ww ];

                              [ "%p"; $_pp ];

                              [ "%r"; $_rr ];

                              [ "%R"; $_R ];

                              [ "%T"; $_T ];

                              [ "%X"; $_X ];

                              [ "%H"; $_H ];

                              [ "%I"; $_I ];

                              [ "%k"; $_kk ];

                              [ "%l"; $_ll ];

                              [ "%M"; $_M ];

                              [ "%S"; $_S ];

                              [ "%o"; $_DDD ];

                              [ "%s"; $_ss ]

                            )

                          )

                          • 10. Re: Extracting Time from a Timestamp
                            beverly

                            it is polite to attribute the original function author(s) and code. Here is where I found

                            php.date()

                            it is based on the PHP date() function

                            beverly