Subscribe to this thread
Home - General / All posts - DateTime.now function
dyalsjas95 post(s)
#10-Feb-18 01:30

Is there an SQL function available to retrieve the current system date and/or time?

Is there an SQL function to retrieve the current time from any NTP time servers?

Does anyone have any scripts that might offer these functions?

Dimitri


4,843 post(s)
#10-Feb-18 12:36

Good questions... Not to my knowledge, but I haven't looked.

Every SQL function that is in 9 is in the query builder. Have you taken a look at that list? Using the Filter box can help searches.

Did you find any such functions there, or discussed in theSQL Functions topic?

It would be great if you could take a look and report back what you found in this thread. Thanks in advance!

dyalsjas95 post(s)
#10-Feb-18 12:52

From the Manifold Help page for SQL Functions,

DateTimeYear(<date>) : <value> Returns the Year value from a datetime string

DateTimeMonth(<date>) : <value> Returns the Month value from a datetime string

DateTimeDay(<date>) : <value> Returns the Day value from a datetime string

DateTimeHour(<date>) : <value> Returns the Hour value from a datetime string

DateTimeMinute(<date>) : <value> Returns the Minute value from a datetime string

DateTimeSecond(<date>) : <value> Returns the Second value from a datetime string

DateTimeMillisecond(<date>) : <value> Returns the Millisecond value from a datetime string.

dyalsjas95 post(s)
#10-Feb-18 13:14

Perhaps it would be useful to have the capability to populate the values of a datetime string from a time source, e.g. current system time or NTP server.

Auto populating (perhaps sequentially) a datetime string from a time source based on the when the geometry of a feature is changed or added to a geometry column may be useful for version management (obviously lots of opportunity for depth here).

The ability to return just the date value from a datetime string (with the ability to call the OS date formatting options) would relieve the requirement to combine three functions right now.

The ability to return just the time value from a datetime string (again, with the ability to call the OS time formatting options) would relieve the requirement to combine three or four functions right now.

DateTime difference functions may also be appreciated, datetime strings are becoming ubiquitous to any sensor output.

DateTime functions are less geo-centric and more SQL data-centric but more robust handling could support further big-data analysis.

Dimitri


4,843 post(s)
#10-Feb-18 13:33

I don't disagree with any of the above, but I wonder about the "may" word. Are these things which you personally would use right now? That's something to consider before you send in a Suggestion.

dyalsjas95 post(s)
#11-Feb-18 17:56

Yes, I would appreciate seeing more functionality added to the current Manifold datetime string capability.

I would use an easy ability to format a datetime string or datetime attribute column as well (as other text formatting tools) when adding supporting tables to a new layout. I would use datetime arithmetic now as another basic and effective query filter for data with datetime attributes. I would use increased functionality of datetime arithmetic as a supporting part of feature level version management processes.

I liked (and used) the full variety of Manifold 8 datetime functions. Templates (with associated SQL code) for these kinds of functions would be much more useful than to just trying to figure out the code from the net. In keeping with the international flavor of Manifold 9, implementing the ability to localize datetime functions using non-Gregorian calendars is another opportunity to build a capability that isn't well integrated into most legacy GIS software.

Effective integration of live geospatial feeds in Manifold projects would seem to require better datetime capabilities; certainly with anything that offers movement tracks. Datetime arithmetic is useful when importing and using sensor data, and could be a very useful factor for conflating updates into big-data sets.

Again, (and as I've posted in other threads in the Cutting Egde forum), I can't speak to the work/benefit ratio perceived by the Manifold development team, but I can't imagine I'm the only use who could find immediate value.

dyalsjas95 post(s)
#12-Feb-18 01:28

BTW being able to add the day of the week as a column (Manifold 8 "Copy day of week from" on the transform toolbar) is a surprisingly useful tool for finding human activity patterns.

Finding the Julian day (Manifold 8 "Copy day of the year from" on the transform toolbar) is also good for supporting a surprising number of repeating human patterns.

Finding the week of the year for a date (Manifold 8 "Copy week from" on the transform toolbar) lets me continue to use Manifold for many non-GIS related table analysis functions.

Frankly, the ability to view and analyze temporal data efficiently is almost as vital as viewing location data. On many occasions, Manifold 8 let me highlight spatial-temporal patterns in ways that legacy GIS just couldn't support.

I also wonder if it might be better to offer an option to separate dates and time as distinct attributes. Yes, dates and times are different scales for measuring the same fundamental value, but we use different measures for distance depending on scale.

I may find opportunities in my work to develop SQL datetime arithmetic queries, but I would rather use a single software (i.e. Manifold 9) to let me accelerate the process.

The realization of how useful this could be means I'll need to be deliberate in composing a Suggestion. If others are have viewpoints on this, please chime in.

So to reiterate from my posting above; Yes, I would appreciate more functionality added to the current Manifold datetime string capability.

adamw


7,903 post(s)
#13-Feb-18 07:27

Thanks for the notes!

We are planning to extend the range of functions working with dates. We have day of week / week of year on the list. Regarding separating date from time, we think 90% of the effect can be accomplished by allowing to format the datetime values to show only date or only time. If that proves not to be enough, we might add separate types.

adamw


7,903 post(s)
#10-Feb-18 15:20

We don't have such a function currently, but we will add it.

The complications are somewhat similar to functions returning random values: we need several moderate additions to the query engine to avoid turning tables produced with the help of such functions into dumb snapshots (we want to keep result tables selectable, editable, etc, as much as possible).

You can currently use something like this:

Script (C#, called 'Script'):

// C#

 

class Script

{

 

static public System.DateTime Now()

{

  return System.DateTime.Now;

}

 

static Manifold.Context Manifold;

static void Main()

{

// code unnecessary, provided for testing

//  Manifold.Application.Log(Now().ToString());

//  Manifold.Application.OpenLog();

}

 

}

Query:

--SQL

FUNCTION Now() DATETIME AS SCRIPT [Script] ENTRY 'Script.Now';

SELECT mfd_id, Now() FROM [mfd_root];

...but that's cheating.

dyalsjas95 post(s)
#10-Feb-18 21:28

So I've gone a few steps further with the and query combination, but it's very granular.

I renamed the script as GetDateTime and declared the function as GetDateTime

FUNCTION GetDateTime()

DATETIME AS SCRIPT [GetDateTime] ENTRY 'Script.Now';

Wrote a new SQL Query that uses the VALUES statement:

VALUES (

GetDateTime(),

DateTimeYear(GetDateTime()),

DateTimeMonth(GetDateTime()),

DateTimeDay(GetDateTime()),

DateTimeHour(GetDateTime()),

DateTimeMinute(GetDateTime()),

DateTimeSecond(GetDateTime())

)

AS

(

[DateTime],

[Year],

[Month],

[Day],

[Hour],

[Minute],

[Second]

);

The result provides each of the elements of the DateTime string in a separate column.

How would I combine the statements to give a date and time in a separate column?

Example 2018-2-10 and 16:50 with column titles Date and Time respectively?

Is there any way to make the query more efficient?

This query takes about a 1 second to run. I presume it's calling the system time repeatedly

Could I make it be faster?

On a separate note, what can I use to format any code snippets I share so the snippets look like the code shared by the Manifold team?

tjhb
8,010 post(s)
online
#11-Feb-18 02:11

On a separate note, what can I use to format any code snippets I share so the snippets look like the code shared by the Manifold team [and other users]?

Use the Insert Code button.

It's slightly tricky.

There is a note on how to use it in the forum FAQ (see the links at the top of the General section and the All Posts sections), and another note here.

Attachments:
Insert Code.png

tjhb
8,010 post(s)
online
#11-Feb-18 02:22

Is there any way to make the query more efficient?

This query takes about a 1 second to run. I presume it's calling the system time repeatedly

Could I make it be faster?

Yes, by calling the function just once, in a subquery, and reusing the result multple times in the outer query. (This also has the advantage that you get just one time value.)

SELECT

    [DateTime],

    DateTimeYear([DateTime]AS [Year],

    DateTimeMonth([DateTime]AS [Month],

    DateTimeDay([DateTime]AS [Day],

    DateTimeHour([DateTime]AS [Hour],

    DateTimeMinute([DateTime]AS [Minute],

    DateTimeSecond([DateTime]AS [Second]

FROM

    (

    VALUES (GetDateTime()) AS ([DateTime])

    )

;

On my system that takes about 0.102s, versus 0.712s for your query with multiple calls.

tjhb
8,010 post(s)
online
#11-Feb-18 02:33

How would I combine the statements to give a date and time in a separate column?

Example 2018-2-10 and 16:50 with column titles Date and Time respectively?

One way is to use another wrap (another subquery level), converting the DATETIME types to NVARCHAR (strings).

SELECT

    [DateTime],

    [Year][Month][Day][Hour][Minute][Second],

    CAST([Year] AS NVARCHAR) & '-' & CAST([Month] AS NVARCHAR) & '-' & CAST([Day] AS NVARCHAR) AS [Date],

    CAST([Hour] AS NVARCHAR) & ':' & CAST([Minute] AS NVARCHAR) AS [Time]

FROM

    (

    SELECT

        [DateTime],

        DateTimeYear([DateTime]AS [Year],

        DateTimeMonth([DateTime]AS [Month],

        DateTimeDay([DateTime]AS [Day],

        DateTimeHour([DateTime]AS [Hour],

        DateTimeMinute([DateTime]AS [Minute],

        DateTimeSecond([DateTime]AS [Second]

    FROM

        (

        VALUES (GetDateTime()) AS ([DateTime])

        )

    )

;

Attachments:
DateTime.sql

dyalsjas95 post(s)
#11-Feb-18 16:58

Thank you!! That's how I thought it could work. I need to learn more more about when and how to use particular Manifold SQL operators. The operators are at the top of the the help page, but I need to figure out how to use them to punctuate my SQL properly. That means I'll make more connections in my head.

lionel

444 post(s)
#11-Feb-18 20:34

Attachments:
datetime_use.png
time_now_.mxb


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

444 post(s)
#10-Feb-18 22:43

thank's for the code !!

Attachments:
Script_now.png
time_now.mxb


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

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