749977_en-US

liliconcom

Problems incrementing each date in a column

Hello, 

I'm importing data with timestamps from and SQLite file, here is a time stamp as an example: 2024-04-12T18:55:42.464558Z. Since JMP doesn't support microseconds I'm trimming the last 8 characters and converting the column into a time format. Everything seems to work up to this point. However, when I attempt to increment each timestamp by 6 hours (time zone differences) all the timestamps are simple deleted. 

 

dt = Current Data Table(); // Gets the currently active data table

// Check if the "timestamp" column exists
If( Contains( dt << Get Column Names("String"), "timestamp"),
    // Get the column reference
    col = Column(dt, "timestamp");

    // Truncate the last 8 characters from each value in the "timestamp" column
    col << Set Each Value( Substr( Char( col ), 1, Length( Char( col ) ) - 8 ) );

    // Convert the truncated timestamps to numeric datetime values
    col << data type(numeric) << modeling type(continuous) << format("yyyy-mm-ddThh:mm:ss");

    // Subtract 6 hours from each datetime value using Date Increment
    For( i = 1, i <= N Rows(dt), i++,
        If( !Is Missing( col[i] ),
            col[i] = Date Increment( col[i], "Hour", -6 );
        );
    );

    Show("Timestamps have been truncated and decremented by 6 hours.");
,
    // Error handling if column does not exist
    Show("Error: 'timestamp' column does not exist in the current data table.")
);

What would be the correct way to basically convert the timestamps into a format that's acceptable for JMP and increment each value by -6? Thank you!

Automation and ScriptingRe: Problems incrementing each date in a column

If you don't want to have milliseconds, I think you could try modifying your SQL query and remove them there.

 

For your solution, have you tried that your script does what you expect it to do until the subtract part? Are the columns changed to numeric, continuous, properly formatted AND JMP recognizes them as dates (change the format to Best and do you get JMP's datenum format).

Re: Problems incrementing each date in a column

Thanks for your input. Got it figured out. The date increment function requires a numeric value so the timestamp had to be converted to the correct data type prior to processing. I also had to change "Hour" to "Second" . Here is the working code if anyone runs into this in the future. 

 

dt = Current Data Table(); // Gets the currently active data table

// Check if the "timestamp" column exists
If( Contains( dt << Get Column Names("String"), "timestamp"),
    // Get the column reference
    col = Column(dt, "timestamp");

    // Process each row and remove the last 8 characters from the timestamp string
    col << Set Each Value( Substr( :timestamp, 1, Length( :timestamp ) - 8 ) );

    Show("Timestamps have been truncated.");
    col << data type(numeric) << modeling type(continuous) << format("yyyy-mm-ddThh:mm:ss");

    // Loop through each row in the column
    For( i = 1, i <= N Rows(dt), i++,
        // Read the value in the "timestamp" column for row i
        timestamp_value = col[i];
        numeric_datetime = Num( timestamp_value, Informat( "yyyy-mm-ddThh:mm:ss" ) );
        numeric_datetime_adjusted = Date Increment(numeric_datetime, "second", -21600);
        col[i] = numeric_datetime_adjusted;
       );
,
    // Error handling if column does not exist
    Show("Error: 'timestamp' column does not exist in the current data table.")
);