Subscribe to this thread
Home - General / All posts - Help with datetime functions please.
bclement
273 post(s)
#16-Sep-22 14:44

How do you split off just the date? I see that there is a function to reset the time portion of a datetime to zero, not what I want. I see that there is a function to make a date however I want it, but that requires the use of at least four functions to get what I want. I do not see mention of a cast function like MS SQL uses to simply cast a datetime to a date. I am at a loss.

It seems that if you have functions that can identify every possible portion of a date (same for time), why can't there be a function that simply takes a datetime and gives you the date and/or the time? I understand from a reply from sales that I don't get "it." Ok, please enlighten me.

Dimitri

7,090 post(s)
#16-Sep-22 15:32

why can't there be a function that simply takes a datetime and gives you the date and/or the time?

There is no such thing as a datetime value that is just a date or just a time. From the Data Types topic:

The datetimetype represents data that contains acalendar date (day, month, year) and time (hour, minute, second, millisecond) ranging from January 3, 0001, 00:00:00.000 to December 31, 9999, 23:59:59.999

The key word there is "and". A datetime value is really a "time" value which is really the number of milliseconds since January 3, 0001, 00L00L00.000 to that particular value. For the convenience of humans it is often expressed as year, month, day, hour, minute, second and milliseconds. But it's an all-in-one data type, not different data types (a data type for years, for months, for days, etc.) in some sort of tuple arrangement.

So, if you have a datetime value you always have the whole thing, or it's not a datetime value. In many database systems (which also means in many applications, including Manifold), there isn't just a date type that is a x3 tuple of year month day, or a time type that is a x4 tuple of hours, minutes, seconds, and milliseconds. SQL Server has a "date" type that's different than datetime. What Oracle calls "date" is really a datetime, but without milliseconds.

If you want a function to return just the date parts of a datetime, what data type is it supposed to return? The logical thing to return is a datetime. But there you may as well just use the original datetime and format the field to show only the date.

If you store it as a datetime, but for some reason you want to "remove" the time part, you'll probably just be storing it with the time part of the datetime being set 0:00:00.000.

To return or to store just the time part, what data type would you use? Float64 to store the number of milliseconds since midnight? A x4 tuple? Why not just store it as datetime where you keep the date portion of it and format the field so that only the time part is shown?

bclement
273 post(s)
#16-Sep-22 18:27

Not sure how to respond here. Pretty sure I have a handle on the information provided and that is not meant as criticism of your explanation in any way. After all, you can't know what I know and so can only document what is and you have done that with your usual accuracy and thoroughness. My question is more abstract?

Given that, to a computer, all constructs concerning a whole host of things what we humans recognize as either dates or times, regardless of format, are just different expressions of milliseconds, that, in my mind, coming from a software user perspective as opposed to the perspective of a software designer, is almost immaterial. Now as also stipulated, all such representations of milliseconds which are designed to be for "the convenience of humans" being mere abstractions of the true nature of the computer's understanding of time and date, being not quite "real" from that perspective, I ask again, why can't there also be a "fake" date and a "fake" time much like there is a "fake" day of week or a "fake" month of year construct or abstraction?

Given our foregoing stipulations, all of the SQL functions listed in the manual are just "fake" or "abstract" translations of a "real" time for the convenience of humans. I am human (depending on who you ask) and I want convenience. In fact, I have grown quite accustomed to it using Manifold. For instance, let's take my address for comparison. The computer knows it for mapping purposes as a series of coordinates which it abstracts to a point on a map that I can recognize given that the map contains enough context. I do not recognize the storage structure of that information in its native form and I will argue that I do not need to unless, for some odd reason, I take up a career in GIS. An important parallel here is the fact that my address could also be stored as a string which the computer does not understand at all, but which is done solely for my edification as a human since it has more meaning to me than say a tuple of coordinates in Euclidean space.

So when I make a request of sales that a SQL function to create a "fake" date, or time be added, how is that any different to asking say for a function to return a day of week that already exists? If someone can come up with a difference, then yes, I do not "get it" and will likely need some serious explanation.

I am pulling data from a database that has a datetime in it but without looking at it, as this is an automation routine and hence negates the formatting option proffered, I need to transform it into a "fake" date which is doubly fake in this case as it will be exported as text in a .csv (like the address) to be displayed in an Excel spreadsheet.

Manifold constantly performs miracles of abstraction well beyond my understanding. That makes me no less aware or appreciative of them. And my request is not in that category as it is rather simple math to convert milliseconds. I say simple not to slight anyone else, but just because I know that in the present company and context, if I can understand it, it is simple indeed.

Finally, if no one wants to make such a SQL function, fine. I will cobble up the make date function along with the other requisite functions to make my own I guess. But again, human -> convenience -> lazy . . .

csb
130 post(s)
#17-Sep-22 00:06

will this work ?

StringFormatDateTime()

the function takes a datetime value and returns a string in numerous formats. 'd' is the one i think you are looking for, but maybe i misunderstand.

bclement
273 post(s)
#17-Sep-22 12:20

That is indeed a better option than I came up with for this application. Thanks.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.