0

There are two variables:

$mm = "01";
$yy = "22";

I need to store it in one column Postges mmyy. What is type mmyy must be if there is not day? Or I can use the first day of month always like: 010122.

Eventually, I want to filter rows with where mmyy > now().

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dmitro
  • 3
  • 2

1 Answers1

1

If you want to treat it like a date/time value, preferably store it as date. That would be:

date '2022-01-01'

Occupies only 4 bytes, same as int4.
Always use ISO format, which is unambiguous regardless of your locale settings. More in the manual.

To convert your variables, you might use the Postgres function to_date():

test=> SELECT to_date('2201', 'YYMM');
  to_date   
------------
 2022-01-01
(1 row)

test=> SELECT to_date('22'::text || '01'::text, 'YYMM');
  to_date   
------------
 2022-01-01
(1 row)

Or prepare a date literal in ISO format.

When compared to now() (which returns timestamp with time zone) the date value is coerced to the first instant of Jan 1st, 2022 at the time zone determined by the current setting of your session. See:

So it works as intended out of the box - except that you possibly haven't thought about time zones, yet ...

For other tasks with date arithmetic, an integer might be a good choice. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228