How to convert the date set from DD:MM:YY HH::MM to morning, afternoon, evening, night in quicksights?

Hi i column in quick sights in the following format: 21/01/2022 9AM 27/01/2022 8pm 4/02/2022 2PM and so on. I want to change them morning, afternoon and night in amazon quicksights. I tried everything that is available in internet but nothing worked for me. As there is HOUR function in quicksight.

Solution

You could use the extract('HH', {date}) function to extract the hour portion from your date field. https://docs.aws.amazon.com/quicksight/latest/user/extract-function.html Then you can use an ifelse() logic to classify the time into day periods.

Example, if your field is called {date}, then you could try something like this:

ifelse(
  extract('HH', {date}) < 6, 'NIGHT',
  extract('HH', {date}) < 12, 'MORNING',
  extract('HH', {date}) < 17, 'ATERNOON',
  extract('HH', {date}) < 20, 'EVENING',
  'NIGHT'
)