where date filter

[BohdanLiuisk]

check if column of type date and use this: https://sqlkata.com/docs/where-date

[BohdanLiuisk]

General Practice: In most modern applications, it is advisable to use timestamptz because it provides more flexibility and helps avoid common pitfalls related to time zone handling. It ensures that your date and time values are consistently represented, regardless of the server's or the user's time zone settings.

Legacy or Specific Cases: Use timestamp only if you have a very specific need to work with naive date and time values without time zone conversions.

DbType -> postgres type DbType.Date (5) -> date DbType.DateTime (6) -> timestamp DbType.DateTime2 (26) -> timestamp DbType.DateTimeOffset (27) -> timestamptz

[BohdanLiuisk]

https://www.postgresql.org/docs/8.1/functions-datetime.html 9.9.1. EXTRACT, date_part

[BohdanLiuisk]

The date type in PostgreSQL only stores the calendar date (YYYY-MM-DD). It does not store time information (hours, minutes, seconds). As a result, attempting to extract the MINUTE part from a date type will either return 0 or cause an error, because there's no time component in a date.

[BohdanLiuisk]

datePart values

• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

[BohdanLiuisk]

request examples:

{
  "entityName": "contact",
  "expressions": [
    {
      "path": "name"
    },
    {
      "path": "email"
    },
    {
      "path": "parent_contact",
      "columns": [
        {
          "path": "name"
        }
      ]
    },
    {
      "path": "created_by",
      "columns": [
        {
          "path": "name"
        }
      ]
    },
    {
      "path": "company",
      "columns": [
        {
          "path": "name"
        },
        {
          "path": "email"
        }
      ]
    }
  ],
  "relatedEntities": [],
  "filter": {
    "type": "group",
    "logical": "or",
    "items": [
      {
        "path": "company.name",
        "predicates": [
          {
            "operator": "isNotNull"
          }
        ]
      },
      {
        "path": "company.email",
        "predicates": [
          {
            "operator": "eq",
            "value": "creatio@gmail.com"
          }
        ]
      },
      {
        "path": "created_on_2",
        "predicates": [
          {
            "datePart": "time",
            "operator": "lte",
            "value": "13:40"
          },
          {
            "datePart": "day",
            "operator": "ne",
            "value": 1
          }
        ]
      },
      {
        "path": "created_on_1",
        "predicates": [
          {
            "operator": "eq",
            "value": "2024-08-23"
          }
        ]
      }
    ]
  },
  "limit": 100,
  "debug": true
}

{
  "entityName": "contact",
  "expressions": [
    {
      "path": "name"
    },
    {
      "path": "email"
    },
    {
      "path": "parent_contact",
      "columns": [
        {
          "path": "name"
        }
      ]
    },
    {
      "path": "created_by",
      "columns": [
        {
          "path": "name"
        }
      ]
    },
    {
      "path": "company",
      "columns": [
        {
          "path": "name"
        },
        {
          "path": "email"
        }
      ]
    }
  ],
  "relatedEntities": [],
  "filter": {
    "path": "created_on_2",
    "predicates": [
      {
        "datePart": "time",
        "operator": "eq",
        "value": "01:51:36"
      }
    ]
  },
  "limit": 100,
  "debug": true
}