Converting a string of numbers to a date Different Senario

[cka]

Hi all, I have read and reread This Thread to try to figure out a possible solution for converting a cdate to an actual date. This thread made sense because I understood the numbers.

But I am stumped because my cdate doesn't make any sense to me and I am hoping it might make sense to some of you more experienced developers.

Cdate 1185444436 =Thursday, 26 July 2007

Cdate 1185590426= Friday, 27 July 2007

How to I make that a readable date? Thanks !

[LaRetta]

Dates as numbers are the number of days from 1/1/0001 to FileMaker. The numbers you've provided are not days because 7/26/2007 as a number is actually 732883. The thread in which you refer is a text field with a true date reversed, ie, YYYYMMDD. That is not your example at all.

The number you display appears to be a timestamp. Change your calculation to result of timestamp. The year is off, however ...

[LaRetta]

Something else is off ... the second number you provided is 7/28 at 2:40:26 AM. If YOU can't tell what the number should be, how can you expect a computer to know? And if you KNOW what the number should be, then we need to know the conversion to provide a calculation for you. :wink2:

[cka]

Thanks! but How did you get 7/28 2:40:26?

From the time stamp on the order it should be

27-Jul-07 19:40?

Actually I think I can do the calculation it's just that I don't understand what the numbers mean?

[cka]

Ok

because of your post (thanks!) I was able to discover that numbers are the often-used UNIX timestamp. ( call time() in PHP) It's the amount of seconds since 01-01-1970 00:00:00.

I think I might need help on that calculation?

[LaRetta]

Well Unix time is difficult to figure (at least for me). I cannot find a consistency in leap from which to determine how to properly adjust it. This Link appears to explain it but it uses a chart. Unless you wish to pull in a table (with the chart data), I can't help you - maybe one of the mathematics masters can.

This works for the example you provided - it's easy to add 1970 to the year but properly adjusting the time is another issue. And, as you'll see, if the time isn't adjusted properly, the date can be up to a day off either direction.

Let (

ts = GetAsTimestamp ( num ) ;

Timestamp ( Date ( Month ( ts ) ; Day ( ts ) ; Year ( ts ) + 1970 ) ; Time ( Hour (ts ) - 7 ; Minute ( ts ) ; Seconds ( ts ) )

)

)

I hope others have ideas.

LaRetta

[comment]

See:

http://www.fmforums.com/forum/showtopic.php?tid/181324

[Fenton]

You can convert numbers to FileMaker Timestamp and back again. It is fairly easy. It's not really a "conversion," it's just two different ways to represent the same thing. GetAsNumber ( Timestamp ), or Timestamp ( number ). (I know it says "text" as the parameter, but it seems to work with a number also).

So, if you wanted to convert numbers representing a Unix date to a FileMaker date you'd add the numbers of Unix's 1st day: 62135596800

Actually I think the Unix timestamp is UTC time. So you'd need to add/subtract the Time() of your time zone.

This works for me:

Let ( [

unixEpoch = Timestamp ( Date ( 1 ; 1 ; 1970 ) ; Time (0 ; 0 ; 0 ) ) ;

offset = - Time ( 8 ; 0 ; 0 )

] ;

GetAsTimestamp ( GetAsNumber ( unixEpoch ) + 1185444436 + offset )

)

// where yourOffset From UTC is the difference, in hours, between your local time and UTC.

[P.S. 1185444436 above (from your 1st post) is your Unix CDate number you want to convert.]

Edited August 12, 2007 by Guest

[cka]

Thanks all! This would have taken me forever to figure out on my own. Your time is much appreciated.

This is what worked for me, since I only needed a date.

GetAsDate (Div ([color:orange]CDate+ 62135596800 -8 * 3600 ; 86400 ) + 1 )

[pnelson]

To take this a bit further, is there a way to lookup a timezone (on the web or otherwise) using fields in your database (say city,state,zip, area code or phone number) and then return that timezone to FileMaker plus insert the correct UTC offsets to get the correct date and time for the current record you are viewing? That would be helpful to me. Are there any externals that do this that anyone knows of?

[comment]

Answered here:

http://fmforums.com/forum/showtopic.php?tid/196185/

Please do not double-post.