Convert Nepali date to english date using postgresql

Question

I have Nepali date in employment table of kep database and i want to convert this date in to English date using postgresql.Guide me please. Here is my table

id   date
1    2071/1/4
2    2071/1/29
3    2069/4/24

Answer 1

SQLFiddle

1) Import list of nepali years and length of each month (I copied data from here). In first column there is nepali year and in other columns there is length of each month in days (second column in table is length of first month in every year).

-- drop table if exists tmpcal;

create table tmpcal (nyear int, a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int);

insert into tmpcal values
(2000 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2001 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2002 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2003 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2004 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2005 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2006 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2007 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2008 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31),
(2009 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2010 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2011 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2012 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30),
(2013 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2014 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2015 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2016 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30),
(2017 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2018 , 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2019 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2020 , 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30),
(2021 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2022 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30),
(2023 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2024 , 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30),
(2025 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2026 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2027 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2028 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2029 , 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30),
(2030 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2031 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2032 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2033 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2034 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2035 , 30, 32, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31),
(2036 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2037 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2038 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2039 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30),
(2040 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2041 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2042 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2043 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30),
(2044 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2045 , 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2046 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2047 , 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30),
(2048 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2049 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30),
(2050 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2051 , 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30),
(2052 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2053 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30),
(2054 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2055 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2056 , 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30),
(2057 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2058 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2059 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2060 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2061 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2062 , 30, 32, 31, 32, 31, 31, 29, 30, 29, 30, 29, 31),
(2063 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2064 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2065 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2066 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31),
(2067 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2068 , 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2069 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2070 , 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30),
(2071 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2072 , 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30),
(2073 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31),
(2074 , 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30),
(2075 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2076 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30),
(2077 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31),
(2078 , 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30),
(2079 , 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30),
(2080 , 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30),
(2081 , 31, 31, 32, 32, 31, 30, 30, 30, 29, 30, 30, 30),
(2082 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30),
(2083 , 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30),
(2084 , 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30),
(2085 , 31, 32, 31, 32, 30, 31, 30, 30, 29, 30, 30, 30),
(2086 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30),
(2087 , 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30),
(2088 , 30, 31, 32, 32, 30, 31, 30, 30, 29, 30, 30, 30),
(2089 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30),
(2090 , 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30);

2) Assign date to every nepali date:

• first convert columns into rows (using union all)
• then we generate nepali days (using generate_series())
• at the end we number rows (using row_number()) and add this number to 1943-04-14 date and substract 1 (used this converter to match nepali date 2000/01/01 to common date).

.

-- drop table if exists cal_conversion;

create table cal_conversion as (

with tmp as (

  select nyear, 1::int as nmonth, a as nday from tmpcal union all
  select nyear, 2, b from tmpcal union all
  select nyear, 3, c from tmpcal union all
  select nyear, 4, d from tmpcal union all
  select nyear, 5, e from tmpcal union all
  select nyear, 6, f from tmpcal union all
  select nyear, 7, g from tmpcal union all
  select nyear, 8, h from tmpcal union all
  select nyear, 9, i from tmpcal union all
  select nyear, 10, j from tmpcal union all
  select nyear, 11, k from tmpcal union all
  select nyear, 12, l from tmpcal

)

select
  *,
  nyear || '/' || nmonth || '/' || nday as ndate,
  '1943-04-14'::date + row_number() over(order by nyear, nmonth, nday)::int - 1 as edate
from (
select 
  nyear,
  nmonth,
  generate_series(1, nday) as nday
   from tmp) x

 );

3) Finally, use our conversion table:

Sample data:

-- drop table if exists test_data;

create table test_data (
  id int,
  ndate varchar);

insert into test_data values
  (1,'2071/1/4'),
  (2,'2071/1/29'),
  (3,'2069/4/24');

Usage (simple join):

select
  ndate,
  id,
  edate
from 
  test_data
  join cal_conversion using (ndate);

Result:

2069/4/24;3;2012-08-08
2071/1/29;2;2014-05-12
2071/1/4;1;2014-04-17