Need to save UTC on insert query into mysql varchar field

[NewMedia]

I already have a key id set as autonumber and this “story_ID” varchar(20) field has to have a Unique value for each record.

I’m getting the “Array to string conversion” error.
I don’t want to set this to a TIMESTAMP format – I want the numeric values with separators stripped.

I am guessing that I should be able to format this in my INSERT API

and I want to concat this with the ID number of the user from another field on new record insert.

I’ve looking over all the “convert array to string” posts over the past few years.
And looking at all the posts about creating various forms of variable numbers. I know I don’t want hashing here – just a “readable” variable in this “story_ID” And the variable string should be limited to maybe 12 numbers.

Any tried-and-true step-by-step solutions someone uses for getting the UTC output into a stripped string on insert action?

Thank you!

[bpj]

Have you tried using the formatDate formatter?

you should be able to do something like {{otherid+'_'+NOW_UTC.formatDate('ddMMyyyyHHmmss')}}
where otherid is the ID field you want to concatenate.

Which would give something like:
1_08042022093621

or for yyyyMMdd order:
{{otherid+'_'+NOW_UTC.formatDate('yyyyMMddHHmmss')}}

giving: 1_20220408093621

[NewMedia]

I will be trying this shortly – here is what just tried before reading your post to just give me a unique number into a varchar field where it took a timestamp value in the timestamp format

select unix_timestamp(stored_zeit) as DateToNumber from story_collection where collection_id = 50;
the retrieved value was 2022-04-09 13:37:28
and this
converted to |DateToNumber||

1649505545

and I tried to put this DateToNumber into my Insert API – it doesn’t error but doesn’t give a result, just an empty value for this field in a new record

I had formatted like this

I also tried it with {{‘unix_timestamp(NOW_UTZ)’}} – same no result

I will check out your solution after a short walk !

[bpj]

I think your mixing up native DB values with Wappler bindings

When you use {{unix_timestamp(NOW_UTZ)}} it is looking for a variable with that name in the server-side code not passing that directly as part of the query.

Have a go with the suggestion above. I have an underscore separating the userid and the timestamp because then you could easily split the value later into user/time if required. You could just concatenate the values without it if you want.

[NewMedia]

{{otherid+'_'+NOW_UTC.formatDate('ddMMyyyyHHmmss')}}

Is this in the Insert Table API

or applied directly in the page code for that input ? –

<input class="form-control" id="inp_story_ID" name="story_ID" aria-describedby="inp_story_ID_help" placeholder="Enter Story ID" dmx-bind:value="{{otherid+'_'+NOW_UTC.formatDate('ddMMyyyyHHmmss')}}">

[NewMedia]

Since the query doesn’t do anything until it arrives at the myaql server that is why I put in a function that typically produces a result when it is run in mysql. It should be interpreted simply as part of the insertion of form valuables on the client side.

I intended to inject that mysql "unix_timestamp(NOW_UTZ) in to the database directly instead of pulling a variable from the form field.

Although that is usually run as

SELECT unix_timestamp(CURRENT_TIME())

[bpj]

But when you add that in the Insert API step, it adds it to a prepared statement not as part of the SQL directly.

The suggested value binding is to be the value inserted in the insert API step for the field where you want to store the id/timestamp value. You will need to replace the ‘otherid’ part with a binding for the id it’s concatenated with.