Блог

Главная - Вопросы по программированию - Преобразовать непальскую дату в английскую с помощью postgresql

Преобразовать непальскую дату в английскую с помощью postgresql

#postgresql #date

#postgresql #Дата

Вопрос:

У меня есть непальская дата в таблице занятости базы данных kep, и я хочу преобразовать эту дату в английскую дату с помощью postgresql. Направьте меня, пожалуйста. Вот моя таблица

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

Ответ №1:

SQLFiddle

1) Импортируйте список непальских лет и продолжительность каждого месяца (я скопировал данные из здесь). В первом столбце указан непальский год, а в других столбцах — продолжительность каждого месяца в днях (второй столбец в таблице — это длина первого месяца в каждом году).

 -- 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) Назначьте дату каждой непальской дате:

  • сначала преобразуйте столбцы в строки (с помощью union all )
  • затем мы генерируем непальские дни (используя generate_series() )
  • в конце мы нумеруем строки (используя row_number() ) и добавляем это число к дате 1943-04-14 и вычитаем 1 (использовали этот конвертер, чтобы сопоставить непальскую дату 2000/01/01 с обычной датой).

.

 -- 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) Наконец, используйте нашу таблицу преобразования:

Пример данных:

 -- 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');

Использование (простое объединение):

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

Результат:

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