DateTime type

[Bill Thoen]

There doesn't seem to be very much documentation on the new DateTime data type and how to use it. I've perused the MB Users and Reference Guides for version 10.5 and 11.0, and  10.5's online help on everything I could find on Date and Time functions and found this topic is not well-covered. I'm looking for the FormatDateTime$() function but it doesn't seem to exist. Is there a formatting function for this type yet or is the format set up in the control panel and maintained by Windows? The best thing I found was this description of the DateTime type:

DateTime is stored in nine bytes: 4 bytes for date, 5 bytes for time. 5 bytes for time include: 2 for millisec, 1 for sec, 1 for min, 1 for hour.

Does anyone know where there's more info about date and time?

TIA,
- Bill Thoen

[robert crossley]

Bill,

 

There is a formatDate$() and a FormateTime$() function that might be of use in combination.

 

I just tried the following on a date time field:

 

FormatDate$(D_HARV_DATE) + " " + FormatTime$(D_HARV_DATE, "h:mm:ss tt")

 

And it gave me some joy.  The format date does not have a lot of flexibility, but that has always been the case.

 

 

R

--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

[Bill Thoen]

Thanks! That works.

I found the real answer too.

The function I was looking for is called gettime( tm as gettime, byval time-format as string). It is listed, but since it is not cross-referenced with datetime() or formatTime$(), it was only by luck that I found it. There is no setTime() function.

For information on format codes for date and/or time, see FormatDate$() and FormatTime$()

A datetime variable is indeed a date structure in the first 4 bytes and a Time structure in the next 5 bytes, so your method:

FormatDate$(D_HARV_DATE) + " " + FormatTime$(D_HARV_DATE, "h:mm:ss tt")

should work fine, too.  It seems also that a date value when stored in a file is an integer representing the number of seconds elapsed since June 1, 1999. Can anyone confirm that I've always thought that Microsoft used Jan 1, 1970 as the start of time.

Anyway to supplement the missing cross-references in the docs I collected every statement and function that had anything to do with date or time, and have listed them below. If anyone notices anything missing, please let me know.

I don't know if any of MapInfo's  read this forun, but it would be very helpful to have the See Also sections in all the reference guide pages so, for example, if you are R'ing TFM on DateTime data types, and there's a See Also xref at the bottom of the page you'll only spend a few seconds discovering that GetTime() is what you want to to use for printing a Time or DataTime type. Otherwise the process can take hours to find the GetTime() function, 'cause it's tuckeed away with the G functions and isn't close to any other time-related functions, so it tends to stay as hidden as Gilligan's Island.



Date & Time Related Functions and Statements
CurDate()
CurDateTime()
CurTime()
Date Window (Statement)
DateWindow()
Day( )
Format$( )
FormatDate$()
FormatTime$()
GetTime()
Hour()
MakeDateTime()
Minute()
Month( )
NumberTo DateTime()
NumberToDate()
Scond()
Set File Timeout (statement)
Set Format (Statement)
StringToDate( )
Time()
Timer( )
Weekday( )
Year( )

Help Page Titles for Date & Time Related Types
Date
DateTime
Dim    (This page does not mention the Time data type, but it should.)
Time


- Bill Thoen

[robert crossley]

Bill,

 

I thought the date value was just an integer in the form of YYYYMMDD, but perhaps it is different behind the scenes.  Are you doing some back-door stuff again?

 

I have mentioned something about the lack of cross-referencing in Mapbasic in the past and got quite flamed for the comment.

 

MapBasic is a very powerful language, but often you only find some of its functions by chance or (usually) from this list.  There is a lot of power there that can be used in SQL in MapInfo as well that would be good if it were more exposed the more advanced punter (eg. area overlap, proportionoverlap to name just a couple).

 

R

--

[Uffe Kousgaard]

I think the easist way to find out is downloading the MITAB source. I am sure there is a comment in there which explains how dates and times are stored at the bit level inside the DAT file.

 

Using seconds since 1999-06-01 would be very unique

 

But actually I thought your description in the first post with hours, minutes, sec, msec etc. was how it looked inside the file?

 

Regards

Uffe Kousgaard

 

From: Bill Thoen

Sent: Thursday, April 26, 2012 2:59 AM

To: mapi...@googlegroups.com

Subject: Re: [MI-L] DateTime type

It seems also that a date value when stored in a file is an integer representing the number of seconds elapsed since June 1, 1999. Can anyone confirm that I've always thought that Microsoft used Jan 1, 1970 as the start of time.

- Bill Thoen

[Peter Horsbøll Møller]

Bill,

Thanks for compiling this list. I'll forward it to our writers and ask them to add the cross references between the time/date functions.

I think I have been over some of them, but I'm sure I didn't find them all

Peter Horsbøll Møller
Pitney Bowes Software

2012/4/26 Bill Thoen <bth...@gisnet.com>

[e.j.h.polle]

Hi Bill, Hi Rob, Hi Uffe, Hi Peter,

Hi List,

The discussion above about Date and Date/Time values above made me think of a C# code sample I have published last year. The method in this sample allows you to convert a Date or a Date/Time value to a long date string, using Regional and Language settings.

Recently I have expanded the sample to also retrieve the name of the weekday or the month of such a value.

You can find the sample here: http://www.twiav.nl/php/mbsampleCsharp.php#csharp (C# and MB source code incuded).

HTH,

Egge-Jan





Op donderdag 26 april 2012 08:05:41 UTC+2 schreef Peter Horsbøll Møller het volgende:

[Bill Thoen]

On 4/26/2012 12:05 AM, Peter Horsbøll Møller wrote:

Bill,

Thanks for compiling this list. I'll forward it to our writers and ask them to add the cross references between the time/date functions.

I think I have been over some of them, but I'm sure I didn't find them all

Thanks Peter,
You might also add this to that:
It looks like I spelled Second() wrong and I left out GetDate().


But I suppose, since they have all the source text if they just run a search through it for keywords like "date" and "time" and the others, they'd probably find everything that's related.

Cheers,

- Bill Thoen

[Bill Thoen]

On 4/25/2012 11:38 PM, Uffe Kousgaard wrote:

I think the easist way to find out is downloading the MITAB source. I am sure there is a comment in there which explains how dates and times are stored at the bit level inside the DAT file.

It just looked so intergery I wsn't thinking

Using seconds since 1999-06-01 would be very unique

Singular, in fact. That's why I don't believe it. But my assumption was that there must be a way to map a 4 byte number in memory to a date. And of coiuorse there is and it's  not a 4-byte integer.

But actually I thought your description in the first post with hours, minutes, sec, msec etc. was how it looked inside the file?

 

That's the Time part. I thought that date would be an integer, because te docs say:

"DateTime is stored in nine bytes: 4 bytes for date, 5 bytes for time. 5 bytes for time include: 2 for millisec, 1 for sec, 1 for min, 1 for hour. " (The Nazghul of Time)  but the binary datetime  value  for April 26, 2012 1:25:33 PM,  looks like:

DC 07 04 1A 00 00 21 19 0D

"4 bytes for date" sounded like integer to me, but on second look, the first two bytes are a smallint for year (07 DC) = 2012, then the next byte is 04 = April, the next 1A = 26, and so thta's the date sorted out. Time comes right after. Two bytes 00 00 here are milliseconds, and note that at most there's a thousand of them, so these bytes will never be larger than E7 03 (999). Next comes seconds &H21 = 33, followed by minutes &H19 = 25, and hours &H0D=13, or 1 PM. And that's Time sorted out.  This also  means there is no zero day.

- Bill Thoen

From: Bill Thoen

Sent: Thursday, April 26, 2012 2:59 AM

To: mapi...@googlegroups.com

Subject: Re: [MI-L] DateTime type

It seems also that a date value when stored in a file is an integer representing the number of seconds elapsed since June 1, 1999. Can anyone confirm that I've always thought that Microsoft used Jan 1, 1970 as the start of time.


- Bill Thoen

--

[Bill Thoen]

On 4/25/2012 7:55 PM, robert crossley wrote:

Bill,

 

I thought the date value was just an integer in the form of YYYYMMDD, but perhaps it is different behind the scenes.  Are you doing some back-door stuff again?

It is a little different behind the scenes.  See previous post for details.  This was just because I got curious, and the answer didn't pop out immediately as I thought t it would. It's how I learn how things work. There's a quote by Will Rogers:

"There are three kinds of men. The one that learns by reading.
The few who learn by observation. The rest of them have to pee
on the electric fence for themselves."

I'm afraid that I'm of the third kind.

I have mentioned something about the lack of cross-referencing in Mapbasic in the past and got quite flamed for the comment.

You don't see that too often in the "civilized" forums any more. There hasn't a been a good Auto De Fé since GIS-L was assimilated. My last day on that forum was a hot one. You can dig around the wayback machine if you want to see the exchnage. Flame wars tend to degenerate into a battle of wits between 2 or 3 unarmed combatants and then somebody brings up  the Nazi simile and it all goes downhill from there.

[Eric Blasenheim]

When Time and Date Time were added in version 9.0, these functions were added:

 

CurTime - returns the current time as a time object
CurDateTime - returns the current date and time as an object
NumberToTime - creates a time object from a nine-digit integer in the form HHMMSSFFF and creates a Time object. For example, 214237582 represents 9:42:37.582 P.M. Similar conceptually to NumberToDate in that the value of a time object is always this kind of value. To see Time in more human readable ways you must format the time to a string.

Hour - retrieves the hour part of a time object as an integer (0-23) 
Minute - retrieves the minute part of a time object as an integer (0-59)
Second - retrieves the second part of a time object as float (0-59.999)
FormatTime$ - returns a string representation of time based on formatting string
NumberToDateTime - creates a date time object from a seventeen-digit integer in the form YYYYMMDDHHMMSSFFF. For example, 20070301214237582 represents March 1, 2007 9:42:37.582 PM. Same notes as for time above.
GetDate - Grabs the Date part of a DateTime object
GetTime - Grabs the Time part of a Date Time object
MakeDateTime - Creates a datetime object from separate Date and Time objects
StringToTime - creates a time object from a string in the format set by your local time settings. So in US usually "hour:min:sec" but is whatever your local settings are.
StringToDateTime - creates a datetime object from a string using the Date and Time string format rules separated by a space.

Note that the StringTo... functions are for completeness and to unambiguous. In general, there is not differece between:

dim vDT as DateTime

vDT = StringToDateTime("04/01/12 13:14:15")

and

vDt = "04/01/12 13:14:15"

Professional will do its best to parse the string in either case.

I will make sure the docs folks are aware of the gaps here.

 

Eric Blasenheim

Pitney Bowes Software

[Eric Blasenheim]

This new Google Groups UI is a bit confusing for me. I find it harder to tell who is saying what.  Anyway, there was some discussion by Bill and Uffe and Rob about storage of Time and Date Time and I am not really sure who had the question. But  I thought I would clarify this. The stated byte sizes for Time (5 bytes) and DateTime (9, Time + 4 for Date) in the documentation are correct in terms of memory size for the relevant MapBasic variables. 

 

Permanent storage is different and depends on the storage destination. On a database, we store all types however the database defines it. No proprietary blobs. However, on Native Tab files which is probably what most here are concerned with, a Time is stored as four bytes (total milliseconds) and therefore a DateTime is 8 bytes.  Date has always been stored in the .DAT file as a binary YYYYMMDD and the same format is used for the Date portion of DateTime.  Like all proprietary data structures, we reserve the right to change them if there was reason to do so.

 

Eric Blasenheim

Pitney Bowes Software

On Wednesday, April 25, 2012 2:11:15 PM UTC-4, Bill wrote: