Function For Current Date Time In Load Job?

GSQL Loading
#1

Hi all,

This should be a really simple thing, maybe I’m taking an entirely wrong approach.

I want to have a load time (from a gsql method) be put as the value for an attribute.

I have a vertex type that has an attribute for a load date time.

My load job has something like this:

…

LOAD foo_file
TO VERTEX FooVertex VALUES (gsql_lower("field1"), gsql_lower(“field1”), gsql_current_time_epoch(0))
USING header=“true”, separator="|";

…

The vertex type is defined as:

CREATE VERTEX FooVertex (PRIMARY_ID id STRING, id STRING, loadDateTime DATETIME)

The gsql_current_time_epoch(0) function from https://docs-beta.tigergraph.com/dev/gsql-ref/ddl-and-loading/creating-a-loading-job#token-functions-for-attribute-expressions

is the closest thing I could find to a “currentDateTime” method to use during a load job, but using gives me a type error:

GSQL > RUN LOADING JOB foo_load_job
Semantic Check Fails: UDF gsql_current_time_epoch is expected to return STRING, but the actual is UINT, when loading vertex FooVertex attribute loadDateTime!

So basically, I need an int to string function, but I could not find one anywhere in the token function documentation.

Anyone else run into this? Or have any way of just putting in the currentDateTime as the value for an attribute during load time?

It seems like it should be super trivial, so maybe I missed something.

Any help would be appreciated.

#2

Hi Brett,

There are two ways you can work around this problem.

  1. You can define the schema to use a UINT data type instead of STRING for the current time field for FooVertex.

  2. You can write your own loading token function and use it to convert the UINT into a STRING. See documentation here: https://docs-beta.tigergraph.com/dev/gsql-ref/ddl-and-loading/creating-a-loading-job#user-defined-token-functions

Option 1 sounds a lot easier, unless you absolutely require the field to be of type STRING.

Thanks,

Kevin

#3

Thanks for the input, I’m not sure if it’s quite right though.

Does DATETIME get treated the same as STRING for a data type while querying, etc?

I would think they are different, which would allow for date time comparisons/searching ranges.

If they are different, I don’t want the type to get loaded as STRING, I want it to get loaded as DATETIME,

the docs say here that the epoch time will get converted to a DATETIME:

https://docs-beta.tigergraph.com/dev/gsql-ref/ddl-and-loading/creating-a-loading-job#loading-a-datetime-attribute

but clearly that doesn’t work because the number is a UINT, not a STRING

(not sure if converting it to STRING will even work since the documentation seems to not be right as it stands).

I don’t know C++, tried messing with the token function, doesn’t compile right, it’s kind of weird because it ties into

other things and I can’t even get the compile to work like how the docs say because there’s some library issue… sigh…

But I feel this shouldn’t even need to come down to making my own function just to be able to get the current date put as the value for a DATETIME field…

#4

Brett,

They are different, as noted in documentation: When loading data into a DATETIME attribute, the GSQL loader will automatically read a string representation of datetime information and convert it to internal datetime representation.

When loading from a file, the epoch seconds will be translated into the DATETIME attribute automatically, but not when you’re getting the current epoch time from the gsql_current_time_epoch() function. We’ll clear this up in our documentation for all future users attempting what you just did.

I’ll provide the steps for both options i mentioned:

  1. You could have avoided any C++ code by simply changing the schema definition to use UINT instead of DATETIME. Once the epoch timestamp is loaded, you could use the epoch_to_datetime (INT int_value ) function to convert the epoch timestamp to a more readable one.

  2. Here is a function that will give you your desired DATETIME format. I got this to work with two Google searches. Please copy paste this code into your TokenBank.cpp file :

extern “C” void sTime (const char* const iToken[], uint32_t iTokenLen[], uint32_t iTokenNum, char* const oToken, uint32_t& oTokenLen) {

// returns datetime in correct format for loading

    time_t rawtime;

    struct tm * timeinfo;

    char buffer [80];

    time (&rawtime);

    timeinfo = localtime(&rawtime);

    strftime(buffer,sizeof(buffer),"%Y-%m-%d %H:%M:%S",timeinfo);

    std::string str(buffer);

    int n = str.length();

    char c_array[n+1];

    int j = 0;

    strcpy(c_array, str.c_str());

    for (int i = 0; i < n; i++) {

            oToken[j++] = c_array[i];

    }

    oTokenLen = j;

}

To use this, instead of calling gsql_current_time_epoch(0), use this : sTime(0).

e.g. load filename to vertex vertex_name values ($0, sTime(0)); where the second datetime attribute is the first attribute after the primary_id.

The compile issues come up when I tested it too, but I’ve loaded up a graph using the function I posted above and it works fine.

Thanks,
Kevin

#5

The function worked perfectly, thank you so much!

In my data, I am maintaining the original source system modification date (which I have no problem loading as a DATETIME),

but I am also trying to capture the current date time (after ETL processes, etc) for when the record gets loaded into TigerGraph, that is why I was needing this function.

Thanks again!