0

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

Dan Hardy
  • 15
  • 3

1 Answers1

0

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

alistaircol
  • 1,433
  • 12
  • 22