0

after few search with my buddy G, I found a lot of solution, but no one corresponds to my situation. Quick explanation : I am currently trying to create a "test table" which will receive 4 millions lines per day. That's around 48 lines/seconds.

To simulate this situation, I want to create a default number for my "LotNumber" column, which will being create based on hour and minutes. Exemple : all the lines created at 9AM and 45min will have the following "LotNumber" : W11409:451

a.k.a W114+HH:MM+1

So, my default column value is :

concat(cast('W114' as char charset utf8mb4) + left(cast(curtime() as char charset utf8mb4),5) + cast('1' as char charset utf8mb4))

And hell yes, that's barbaric. My "LotNumber" column is VARCHAR,

When I create a single line, HeidiSQL send error message 1292.

I am new to SQL, and I have no idea where my error is.

Phaeron
  • 33
  • 6

1 Answers1

0

Your code works, just separate the data elements by , instead of +

select concat(
  cast('W114' as char charset utf8mb4),
  left(cast(curtime() as char charset utf8mb4),5),
  cast('1' as char charset utf8mb4)
  )

You can test on this db<>fiddle

James
  • 2,954
  • 2
  • 12
  • 25
  • Hey James, thanks for answering quickly. I changed my code, but when I add a line, I have this new error : 1292, Truncated Incorrect Time Value : '' I try with/without "select" you add. Maybe because I am in Default value ? – Phaeron Jan 26 '22 at 09:15
  • Can you replicate your query in some Maria Db on db<>fiddle. As I shared you, the query changing just those little points works. – James Jan 26 '22 at 09:41
  • Ok, well, that's work when I use a query from console, and since beginning I am creating line by my own, with right click... and this doesn't work well. Thanks you for your useful help. – Phaeron Jan 26 '22 at 10:36