Search code examples
mysqlstringsql-timestamp

How to Create text string in MySQL against all records in a view

I'm looking to see if I can create a view where I can create a third column that always shows a text string made up of the Current Year-Current WeekNo so a sample would be 2021-26 or 2021-23.

The whole point is that I effectively need to timestamp set columns against the year-week so the view would be

Column1 - Type Column2 - Value Column3 - year-week

An event then copies this info to another table where the I have an accurate time stamp in the format year-week.

MySqls own timestamp is not relevant for this task and I can get the single result using (select WEEK(CURDATE(),0)) as 'Week_No', (SELECT YEAR(CURDATE())) as 'Year'

Any input would be good. Thanks

Solution

  • You could try adding something like this to the field list:

    concat(year(curdate()), '-', week(curdate(), 1)) as 'year_week'
    • week has various options for how to show/calculate the week number.
    • dual is a dummy table name in situations where no tables are referenced

    enter image description here

    e.g.:

    select
    'value' as value,
    'type' as type,
    concat(year(curdate()), '-', week(curdate(), 1)) as 'year_week'
from dual

    Demo: http://sqlfiddle.com/#!9/9eecb/227666