포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ SESSIONTIMEZONE / SYSTIMESTAMP Oracle Function ]
SESSIONTIMEZONE함수는 현재 세션의 표준 시간대(time zone)를 반환한다. 반환 타입은 최근의 Alter Session 문장에서 사용자가 지정한 표준 시간대 값에 따라 시간대 offset(‘[+|]TZH:THM’형태의 문자형) 또는 시간대 지역명을 반환한다.
1. Oracle(오라클)
현재 세션의 표준 시간대를 반환한다.
| Oracle Programming |
SELECT SESSIONTIMEZONE
FROM DUAL;
| Results |
SESSIONTIMEZONE
------------------
US/Pacific
2. Python Pandas(파이썬)
pytz.localize()
현재 세션의 표준 시간대를 반환한다.
| Python Programming |
import datetime
from pytz import utc
utc.localize(datetime.datetime.utcnow())
| Results |
datetime.datetime(2021, 2, 11, 23, 39, 29, 688262, tzinfo=<UTC>)
tzinfo
| Python Programming |
import datetime
LOCAL_TIMEZONE = datetime.datetime.now(datetime.timezone.utc).astimezone().tzinfo
LOCAL_TIMEZONE
| Results |
datetime.timezone(datetime.timedelta(seconds=32400), '대한민국 표준시')
get_localzone()
| Python Programming |
from tzlocal import get_localzone
local = get_localzone()
local
| Results |
<DstTzInfo 'Asia/Seoul' LMT+8:28:00 STD>
time.tzname
| Python Programming |
import time
time.tzname
| Results |
('대한민국 표준시', '대한민국 일광 절약 시간')
tzname()
| Python Programming |
import datetime
datetime.datetime.now(datetime.timezone.utc).astimezone().tzname()
| Results |
'대한민국 표준시'
pytz.all_timezones
pytz 패키지에서 제공하는 시간대를 확인 할 수 있다.
| Python Programming |
import pytz
timezones=pytz.all_timezones
timezones
| Results |
['Africa/Abidjan',
'Africa/Accra',
'Africa/Addis_Ababa',
'Africa/Algiers',
'Africa/Asmara',
'Africa/Asmera',
'Africa/Bamako',
'Africa/Bangui',
'Africa/Banjul',
'Africa/Bissau',
'Africa/Blantyre',
'Africa/Brazzaville',
'Africa/Bujumbura',
'Africa/Cairo',
'Africa/Casablanca',
'Africa/Ceuta',
'Africa/Conakry',
'Africa/Dakar',
'Africa/Dar_es_Salaam',
'Africa/Djibouti',
'Africa/Douala',
'Africa/El_Aaiun',
'Africa/Freetown',
'Africa/Gaborone',
'Africa/Harare',
'Africa/Johannesburg',
'Africa/Juba',
'Africa/Kampala',
'Africa/Khartoum',
'Africa/Kigali',
'Africa/Kinshasa',
'Africa/Lagos',
'Africa/Libreville',
'Africa/Lome',
'Africa/Luanda',
'Africa/Lubumbashi',
'Africa/Lusaka',
'Africa/Malabo',
'Africa/Maputo',
'Africa/Maseru',
'Africa/Mbabane',
'Africa/Mogadishu',
'Africa/Monrovia',
'Africa/Nairobi',
'Africa/Ndjamena',
'Africa/Niamey',
'Africa/Nouakchott',
'Africa/Ouagadougou',
'Africa/Porto-Novo',
'Africa/Sao_Tome',
'Africa/Timbuktu',
'Africa/Tripoli',
'Africa/Tunis',
'Africa/Windhoek',
'America/Adak',
'America/Anchorage',
'America/Anguilla',
'America/Antigua',
'America/Araguaina',
'America/Argentina/Buenos_Aires',
'America/Argentina/Catamarca',
'America/Argentina/ComodRivadavia',
'America/Argentina/Cordoba',
'America/Argentina/Jujuy',
'America/Argentina/La_Rioja',
'America/Argentina/Mendoza',
'America/Argentina/Rio_Gallegos',
'America/Argentina/Salta',
'America/Argentina/San_Juan',
'America/Argentina/San_Luis',
'America/Argentina/Tucuman',
'America/Argentina/Ushuaia',
'America/Aruba',
'America/Asuncion',
'America/Atikokan',
'America/Atka',
'America/Bahia',
'America/Bahia_Banderas',
'America/Barbados',
'America/Belem',
'America/Belize',
'America/Blanc-Sablon',
'America/Boa_Vista',
'America/Bogota',
'America/Boise',
'America/Buenos_Aires',
'America/Cambridge_Bay',
'America/Campo_Grande',
'America/Cancun',
'America/Caracas',
'America/Catamarca',
'America/Cayenne',
'America/Cayman',
'America/Chicago',
'America/Chihuahua',
'America/Coral_Harbour',
'America/Cordoba',
'America/Costa_Rica',
'America/Creston',
'America/Cuiaba',
'America/Curacao',
'America/Danmarkshavn',
'America/Dawson',
'America/Dawson_Creek',
'America/Denver',
'America/Detroit',
'America/Dominica',
'America/Edmonton',
'America/Eirunepe',
'America/El_Salvador',
'America/Ensenada',
'America/Fort_Nelson',
'America/Fort_Wayne',
'America/Fortaleza',
'America/Glace_Bay',
'America/Godthab',
'America/Goose_Bay',
'America/Grand_Turk',
'America/Grenada',
'America/Guadeloupe',
'America/Guatemala',
'America/Guayaquil',
'America/Guyana',
'America/Halifax',
'America/Havana',
'America/Hermosillo',
'America/Indiana/Indianapolis',
'America/Indiana/Knox',
'America/Indiana/Marengo',
'America/Indiana/Petersburg',
'America/Indiana/Tell_City',
'America/Indiana/Vevay',
'America/Indiana/Vincennes',
'America/Indiana/Winamac',
'America/Indianapolis',
'America/Inuvik',
'America/Iqaluit',
'America/Jamaica',
'America/Jujuy',
'America/Juneau',
'America/Kentucky/Louisville',
'America/Kentucky/Monticello',
'America/Knox_IN',
'America/Kralendijk',
'America/La_Paz',
'America/Lima',
'America/Los_Angeles',
'America/Louisville',
'America/Lower_Princes',
'America/Maceio',
'America/Managua',
'America/Manaus',
'America/Marigot',
'America/Martinique',
'America/Matamoros',
'America/Mazatlan',
'America/Mendoza',
'America/Menominee',
'America/Merida',
'America/Metlakatla',
'America/Mexico_City',
'America/Miquelon',
'America/Moncton',
'America/Monterrey',
'America/Montevideo',
'America/Montreal',
'America/Montserrat',
'America/Nassau',
'America/New_York',
'America/Nipigon',
'America/Nome',
'America/Noronha',
'America/North_Dakota/Beulah',
'America/North_Dakota/Center',
'America/North_Dakota/New_Salem',
'America/Nuuk',
'America/Ojinaga',
'America/Panama',
'America/Pangnirtung',
'America/Paramaribo',
'America/Phoenix',
'America/Port-au-Prince',
'America/Port_of_Spain',
'America/Porto_Acre',
'America/Porto_Velho',
'America/Puerto_Rico',
'America/Punta_Arenas',
'America/Rainy_River',
'America/Rankin_Inlet',
'America/Recife',
'America/Regina',
'America/Resolute',
'America/Rio_Branco',
'America/Rosario',
'America/Santa_Isabel',
'America/Santarem',
'America/Santiago',
'America/Santo_Domingo',
'America/Sao_Paulo',
'America/Scoresbysund',
'America/Shiprock',
'America/Sitka',
'America/St_Barthelemy',
'America/St_Johns',
'America/St_Kitts',
'America/St_Lucia',
'America/St_Thomas',
'America/St_Vincent',
'America/Swift_Current',
'America/Tegucigalpa',
'America/Thule',
'America/Thunder_Bay',
'America/Tijuana',
'America/Toronto',
'America/Tortola',
'America/Vancouver',
'America/Virgin',
'America/Whitehorse',
'America/Winnipeg',
'America/Yakutat',
'America/Yellowknife',
'Antarctica/Casey',
'Antarctica/Davis',
'Antarctica/DumontDUrville',
'Antarctica/Macquarie',
'Antarctica/Mawson',
'Antarctica/McMurdo',
'Antarctica/Palmer',
'Antarctica/Rothera',
'Antarctica/South_Pole',
'Antarctica/Syowa',
'Antarctica/Troll',
'Antarctica/Vostok',
'Arctic/Longyearbyen',
'Asia/Aden',
'Asia/Almaty',
'Asia/Amman',
'Asia/Anadyr',
'Asia/Aqtau',
'Asia/Aqtobe',
'Asia/Ashgabat',
'Asia/Ashkhabad',
'Asia/Atyrau',
'Asia/Baghdad',
'Asia/Bahrain',
'Asia/Baku',
'Asia/Bangkok',
'Asia/Barnaul',
'Asia/Beirut',
'Asia/Bishkek',
'Asia/Brunei',
'Asia/Calcutta',
'Asia/Chita',
'Asia/Choibalsan',
'Asia/Chongqing',
'Asia/Chungking',
'Asia/Colombo',
'Asia/Dacca',
'Asia/Damascus',
'Asia/Dhaka',
'Asia/Dili',
'Asia/Dubai',
'Asia/Dushanbe',
'Asia/Famagusta',
'Asia/Gaza',
'Asia/Harbin',
'Asia/Hebron',
'Asia/Ho_Chi_Minh',
'Asia/Hong_Kong',
'Asia/Hovd',
'Asia/Irkutsk',
'Asia/Istanbul',
'Asia/Jakarta',
'Asia/Jayapura',
'Asia/Jerusalem',
'Asia/Kabul',
'Asia/Kamchatka',
'Asia/Karachi',
'Asia/Kashgar',
'Asia/Kathmandu',
'Asia/Katmandu',
'Asia/Khandyga',
'Asia/Kolkata',
'Asia/Krasnoyarsk',
'Asia/Kuala_Lumpur',
'Asia/Kuching',
'Asia/Kuwait',
'Asia/Macao',
'Asia/Macau',
'Asia/Magadan',
'Asia/Makassar',
'Asia/Manila',
'Asia/Muscat',
'Asia/Nicosia',
'Asia/Novokuznetsk',
'Asia/Novosibirsk',
'Asia/Omsk',
'Asia/Oral',
'Asia/Phnom_Penh',
'Asia/Pontianak',
'Asia/Pyongyang',
'Asia/Qatar',
'Asia/Qostanay',
'Asia/Qyzylorda',
'Asia/Rangoon',
'Asia/Riyadh',
'Asia/Saigon',
'Asia/Sakhalin',
'Asia/Samarkand',
'Asia/Seoul',
'Asia/Shanghai',
'Asia/Singapore',
'Asia/Srednekolymsk',
'Asia/Taipei',
'Asia/Tashkent',
'Asia/Tbilisi',
'Asia/Tehran',
'Asia/Tel_Aviv',
'Asia/Thimbu',
'Asia/Thimphu',
'Asia/Tokyo',
'Asia/Tomsk',
'Asia/Ujung_Pandang',
'Asia/Ulaanbaatar',
'Asia/Ulan_Bator',
'Asia/Urumqi',
'Asia/Ust-Nera',
'Asia/Vientiane',
'Asia/Vladivostok',
'Asia/Yakutsk',
'Asia/Yangon',
'Asia/Yekaterinburg',
'Asia/Yerevan',
'Atlantic/Azores',
'Atlantic/Bermuda',
'Atlantic/Canary',
'Atlantic/Cape_Verde',
'Atlantic/Faeroe',
'Atlantic/Faroe',
'Atlantic/Jan_Mayen',
'Atlantic/Madeira',
'Atlantic/Reykjavik',
'Atlantic/South_Georgia',
'Atlantic/St_Helena',
'Atlantic/Stanley',
'Australia/ACT',
'Australia/Adelaide',
'Australia/Brisbane',
'Australia/Broken_Hill',
'Australia/Canberra',
'Australia/Currie',
'Australia/Darwin',
'Australia/Eucla',
'Australia/Hobart',
'Australia/LHI',
'Australia/Lindeman',
'Australia/Lord_Howe',
'Australia/Melbourne',
'Australia/NSW',
'Australia/North',
'Australia/Perth',
'Australia/Queensland',
'Australia/South',
'Australia/Sydney',
'Australia/Tasmania',
'Australia/Victoria',
'Australia/West',
'Australia/Yancowinna',
'Brazil/Acre',
'Brazil/DeNoronha',
'Brazil/East',
'Brazil/West',
'CET',
'CST6CDT',
'Canada/Atlantic',
'Canada/Central',
'Canada/Eastern',
'Canada/Mountain',
'Canada/Newfoundland',
'Canada/Pacific',
'Canada/Saskatchewan',
'Canada/Yukon',
'Chile/Continental',
'Chile/EasterIsland',
'Cuba',
'EET',
'EST',
'EST5EDT',
'Egypt',
'Eire',
'Etc/GMT',
'Etc/GMT+0',
'Etc/GMT+1',
'Etc/GMT+10',
'Etc/GMT+11',
'Etc/GMT+12',
'Etc/GMT+2',
'Etc/GMT+3',
'Etc/GMT+4',
'Etc/GMT+5',
'Etc/GMT+6',
'Etc/GMT+7',
'Etc/GMT+8',
'Etc/GMT+9',
'Etc/GMT-0',
'Etc/GMT-1',
'Etc/GMT-10',
'Etc/GMT-11',
'Etc/GMT-12',
'Etc/GMT-13',
'Etc/GMT-14',
'Etc/GMT-2',
'Etc/GMT-3',
'Etc/GMT-4',
'Etc/GMT-5',
'Etc/GMT-6',
'Etc/GMT-7',
'Etc/GMT-8',
'Etc/GMT-9',
'Etc/GMT0',
'Etc/Greenwich',
'Etc/UCT',
'Etc/UTC',
'Etc/Universal',
'Etc/Zulu',
'Europe/Amsterdam',
'Europe/Andorra',
'Europe/Astrakhan',
'Europe/Athens',
'Europe/Belfast',
'Europe/Belgrade',
'Europe/Berlin',
'Europe/Bratislava',
'Europe/Brussels',
'Europe/Bucharest',
'Europe/Budapest',
'Europe/Busingen',
'Europe/Chisinau',
'Europe/Copenhagen',
'Europe/Dublin',
'Europe/Gibraltar',
'Europe/Guernsey',
'Europe/Helsinki',
'Europe/Isle_of_Man',
'Europe/Istanbul',
'Europe/Jersey',
'Europe/Kaliningrad',
'Europe/Kiev',
'Europe/Kirov',
'Europe/Lisbon',
'Europe/Ljubljana',
'Europe/London',
'Europe/Luxembourg',
'Europe/Madrid',
'Europe/Malta',
'Europe/Mariehamn',
'Europe/Minsk',
'Europe/Monaco',
'Europe/Moscow',
'Europe/Nicosia',
'Europe/Oslo',
'Europe/Paris',
'Europe/Podgorica',
'Europe/Prague',
'Europe/Riga',
'Europe/Rome',
'Europe/Samara',
'Europe/San_Marino',
'Europe/Sarajevo',
'Europe/Saratov',
'Europe/Simferopol',
'Europe/Skopje',
'Europe/Sofia',
'Europe/Stockholm',
'Europe/Tallinn',
'Europe/Tirane',
'Europe/Tiraspol',
'Europe/Ulyanovsk',
'Europe/Uzhgorod',
'Europe/Vaduz',
'Europe/Vatican',
'Europe/Vienna',
'Europe/Vilnius',
'Europe/Volgograd',
'Europe/Warsaw',
'Europe/Zagreb',
'Europe/Zaporozhye',
'Europe/Zurich',
'GB',
'GB-Eire',
'GMT',
'GMT+0',
'GMT-0',
'GMT0',
'Greenwich',
'HST',
'Hongkong',
'Iceland',
'Indian/Antananarivo',
'Indian/Chagos',
'Indian/Christmas',
'Indian/Cocos',
'Indian/Comoro',
'Indian/Kerguelen',
'Indian/Mahe',
'Indian/Maldives',
'Indian/Mauritius',
'Indian/Mayotte',
'Indian/Reunion',
'Iran',
'Israel',
'Jamaica',
'Japan',
'Kwajalein',
'Libya',
'MET',
'MST',
'MST7MDT',
'Mexico/BajaNorte',
'Mexico/BajaSur',
'Mexico/General',
'NZ',
'NZ-CHAT',
'Navajo',
'PRC',
'PST8PDT',
'Pacific/Apia',
'Pacific/Auckland',
'Pacific/Bougainville',
'Pacific/Chatham',
'Pacific/Chuuk',
'Pacific/Easter',
'Pacific/Efate',
'Pacific/Enderbury',
'Pacific/Fakaofo',
'Pacific/Fiji',
'Pacific/Funafuti',
'Pacific/Galapagos',
'Pacific/Gambier',
'Pacific/Guadalcanal',
'Pacific/Guam',
'Pacific/Honolulu',
'Pacific/Johnston',
'Pacific/Kiritimati',
'Pacific/Kosrae',
'Pacific/Kwajalein',
'Pacific/Majuro',
'Pacific/Marquesas',
'Pacific/Midway',
'Pacific/Nauru',
'Pacific/Niue',
'Pacific/Norfolk',
'Pacific/Noumea',
'Pacific/Pago_Pago',
'Pacific/Palau',
'Pacific/Pitcairn',
'Pacific/Pohnpei',
'Pacific/Ponape',
'Pacific/Port_Moresby',
'Pacific/Rarotonga',
'Pacific/Saipan',
'Pacific/Samoa',
'Pacific/Tahiti',
'Pacific/Tarawa',
'Pacific/Tongatapu',
'Pacific/Truk',
'Pacific/Wake',
'Pacific/Wallis',
'Pacific/Yap',
'Poland',
'Portugal',
'ROC',
'ROK',
'Singapore',
'Turkey',
'UCT',
'US/Alaska',
'US/Aleutian',
'US/Arizona',
'US/Central',
'US/East-Indiana',
'US/Eastern',
'US/Hawaii',
'US/Indiana-Starke',
'US/Michigan',
'US/Mountain',
'US/Pacific',
'US/Samoa',
'UTC',
'Universal',
'W-SU',
'WET',
'Zulu']
[참고] time_struct 형태 변환
입력된 초를 UTC(협정 세계표준시) 기준의 time_struct 형태로 변환환다.
| Python Programming |
from time import gmtime, strftime
gmtime(0)
| Results |
time.struct_time(tm_year=1970, tm_mon=1, tm_mday=1, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=3, tm_yday=1, tm_isdst=0)
3. R Programming (R Package)
현재 세션의 표준 시간대를 반환한다.
- 대한민국 표준시 (KST-Korea stabdar time)의 현재 시간을 반환한다.
| R Programming |
%%R
Sys.time()
| Results |
[1] "2020-12-14 22:02:01 KST"
Sys.timezone()
| R Programming |
%%R
print( Sys.timezone() )
Sys.timezone(location = TRUE)
| Results |
[1] "Asia/Seoul"
[1] "Asia/Seoul"
as.POSIXct()
| R Programming |
%%R
as.POSIXct(Sys.time())
| Results |
[1] "2020-12-14 22:02:22 KST"
America/New_York 시간대
America/New_York 시간대를 따르는 날짜/시간 데이터를 생성한다.
| R Programming |
%%R
x <- ymd_hms("2020-08-07 00:00:01", tz = "America/New_York")
print ( with_tz(x, "GMT") )
tz(x)
| Results |
[1] "2020-08-07 04:00:01 GMT"
[1] "America/New_York"
lubridate 패키지
lubridate 패키지에서 지원하는 모든 시간대를 확인 할 수 있다.
| R Programming |
%%R
library(lubridate)
OlsonNames()
| Results |
[1] "Africa/Abidjan" "Africa/Accra"
[3] "Africa/Addis_Ababa" "Africa/Algiers"
[5] "Africa/Asmara" "Africa/Asmera"
[7] "Africa/Bamako" "Africa/Bangui"
[9] "Africa/Banjul" "Africa/Bissau"
[11] "Africa/Blantyre" "Africa/Brazzaville"
[13] "Africa/Bujumbura" "Africa/Cairo"
[15] "Africa/Casablanca" "Africa/Ceuta"
[17] "Africa/Conakry" "Africa/Dakar"
[19] "Africa/Dar_es_Salaam" "Africa/Djibouti"
[21] "Africa/Douala" "Africa/El_Aaiun"
[23] "Africa/Freetown" "Africa/Gaborone"
[25] "Africa/Harare" "Africa/Johannesburg"
[27] "Africa/Juba" "Africa/Kampala"
[29] "Africa/Khartoum" "Africa/Kigali"
[31] "Africa/Kinshasa" "Africa/Lagos"
[33] "Africa/Libreville" "Africa/Lome"
[35] "Africa/Luanda" "Africa/Lubumbashi"
[37] "Africa/Lusaka" "Africa/Malabo"
[39] "Africa/Maputo" "Africa/Maseru"
[41] "Africa/Mbabane" "Africa/Mogadishu"
[43] "Africa/Monrovia" "Africa/Nairobi"
[45] "Africa/Ndjamena" "Africa/Niamey"
[47] "Africa/Nouakchott" "Africa/Ouagadougou"
[49] "Africa/Porto-Novo" "Africa/Sao_Tome"
[51] "Africa/Timbuktu" "Africa/Tripoli"
[53] "Africa/Tunis" "Africa/Windhoek"
[55] "America/Adak" "America/Anchorage"
[57] "America/Anguilla" "America/Antigua"
[59] "America/Araguaina" "America/Argentina/Buenos_Aires"
[61] "America/Argentina/Catamarca" "America/Argentina/ComodRivadavia"
[63] "America/Argentina/Cordoba" "America/Argentina/Jujuy"
[65] "America/Argentina/La_Rioja" "America/Argentina/Mendoza"
[67] "America/Argentina/Rio_Gallegos" "America/Argentina/Salta"
[69] "America/Argentina/San_Juan" "America/Argentina/San_Luis"
[71] "America/Argentina/Tucuman" "America/Argentina/Ushuaia"
[73] "America/Aruba" "America/Asuncion"
[75] "America/Atikokan" "America/Atka"
[77] "America/Bahia" "America/Bahia_Banderas"
[79] "America/Barbados" "America/Belem"
[81] "America/Belize" "America/Blanc-Sablon"
[83] "America/Boa_Vista" "America/Bogota"
[85] "America/Boise" "America/Buenos_Aires"
[87] "America/Cambridge_Bay" "America/Campo_Grande"
[89] "America/Cancun" "America/Caracas"
[91] "America/Catamarca" "America/Cayenne"
[93] "America/Cayman" "America/Chicago"
[95] "America/Chihuahua" "America/Coral_Harbour"
[97] "America/Cordoba" "America/Costa_Rica"
[99] "America/Creston" "America/Cuiaba"
[101] "America/Curacao" "America/Danmarkshavn"
[103] "America/Dawson" "America/Dawson_Creek"
[105] "America/Denver" "America/Detroit"
[107] "America/Dominica" "America/Edmonton"
[109] "America/Eirunepe" "America/El_Salvador"
[111] "America/Ensenada" "America/Fort_Nelson"
[113] "America/Fort_Wayne" "America/Fortaleza"
[115] "America/Glace_Bay" "America/Godthab"
[117] "America/Goose_Bay" "America/Grand_Turk"
[119] "America/Grenada" "America/Guadeloupe"
[121] "America/Guatemala" "America/Guayaquil"
[123] "America/Guyana" "America/Halifax"
[125] "America/Havana" "America/Hermosillo"
[127] "America/Indiana/Indianapolis" "America/Indiana/Knox"
[129] "America/Indiana/Marengo" "America/Indiana/Petersburg"
[131] "America/Indiana/Tell_City" "America/Indiana/Vevay"
[133] "America/Indiana/Vincennes" "America/Indiana/Winamac"
[135] "America/Indianapolis" "America/Inuvik"
[137] "America/Iqaluit" "America/Jamaica"
[139] "America/Jujuy" "America/Juneau"
[141] "America/Kentucky/Louisville" "America/Kentucky/Monticello"
[143] "America/Knox_IN" "America/Kralendijk"
[145] "America/La_Paz" "America/Lima"
[147] "America/Los_Angeles" "America/Louisville"
[149] "America/Lower_Princes" "America/Maceio"
[151] "America/Managua" "America/Manaus"
[153] "America/Marigot" "America/Martinique"
[155] "America/Matamoros" "America/Mazatlan"
[157] "America/Mendoza" "America/Menominee"
[159] "America/Merida" "America/Metlakatla"
[161] "America/Mexico_City" "America/Miquelon"
[163] "America/Moncton" "America/Monterrey"
[165] "America/Montevideo" "America/Montreal"
[167] "America/Montserrat" "America/Nassau"
[169] "America/New_York" "America/Nipigon"
[171] "America/Nome" "America/Noronha"
[173] "America/North_Dakota/Beulah" "America/North_Dakota/Center"
[175] "America/North_Dakota/New_Salem" "America/Nuuk"
[177] "America/Ojinaga" "America/Panama"
[179] "America/Pangnirtung" "America/Paramaribo"
[181] "America/Phoenix" "America/Port-au-Prince"
[183] "America/Port_of_Spain" "America/Porto_Acre"
[185] "America/Porto_Velho" "America/Puerto_Rico"
[187] "America/Punta_Arenas" "America/Rainy_River"
[189] "America/Rankin_Inlet" "America/Recife"
[191] "America/Regina" "America/Resolute"
[193] "America/Rio_Branco" "America/Rosario"
[195] "America/Santa_Isabel" "America/Santarem"
[197] "America/Santiago" "America/Santo_Domingo"
[199] "America/Sao_Paulo" "America/Scoresbysund"
[201] "America/Shiprock" "America/Sitka"
[203] "America/St_Barthelemy" "America/St_Johns"
[205] "America/St_Kitts" "America/St_Lucia"
[207] "America/St_Thomas" "America/St_Vincent"
[209] "America/Swift_Current" "America/Tegucigalpa"
[211] "America/Thule" "America/Thunder_Bay"
[213] "America/Tijuana" "America/Toronto"
[215] "America/Tortola" "America/Vancouver"
[217] "America/Virgin" "America/Whitehorse"
[219] "America/Winnipeg" "America/Yakutat"
[221] "America/Yellowknife" "Antarctica/Casey"
[223] "Antarctica/Davis" "Antarctica/DumontDUrville"
[225] "Antarctica/Macquarie" "Antarctica/Mawson"
[227] "Antarctica/McMurdo" "Antarctica/Palmer"
[229] "Antarctica/Rothera" "Antarctica/South_Pole"
[231] "Antarctica/Syowa" "Antarctica/Troll"
[233] "Antarctica/Vostok" "Arctic/Longyearbyen"
[235] "Asia/Aden" "Asia/Almaty"
[237] "Asia/Amman" "Asia/Anadyr"
[239] "Asia/Aqtau" "Asia/Aqtobe"
[241] "Asia/Ashgabat" "Asia/Ashkhabad"
[243] "Asia/Atyrau" "Asia/Baghdad"
[245] "Asia/Bahrain" "Asia/Baku"
[247] "Asia/Bangkok" "Asia/Barnaul"
[249] "Asia/Beirut" "Asia/Bishkek"
[251] "Asia/Brunei" "Asia/Calcutta"
[253] "Asia/Chita" "Asia/Choibalsan"
[255] "Asia/Chongqing" "Asia/Chungking"
[257] "Asia/Colombo" "Asia/Dacca"
[259] "Asia/Damascus" "Asia/Dhaka"
[261] "Asia/Dili" "Asia/Dubai"
[263] "Asia/Dushanbe" "Asia/Famagusta"
[265] "Asia/Gaza" "Asia/Harbin"
[267] "Asia/Hebron" "Asia/Ho_Chi_Minh"
[269] "Asia/Hong_Kong" "Asia/Hovd"
[271] "Asia/Irkutsk" "Asia/Istanbul"
[273] "Asia/Jakarta" "Asia/Jayapura"
[275] "Asia/Jerusalem" "Asia/Kabul"
[277] "Asia/Kamchatka" "Asia/Karachi"
[279] "Asia/Kashgar" "Asia/Kathmandu"
[281] "Asia/Katmandu" "Asia/Khandyga"
[283] "Asia/Kolkata" "Asia/Krasnoyarsk"
[285] "Asia/Kuala_Lumpur" "Asia/Kuching"
[287] "Asia/Kuwait" "Asia/Macao"
[289] "Asia/Macau" "Asia/Magadan"
[291] "Asia/Makassar" "Asia/Manila"
[293] "Asia/Muscat" "Asia/Nicosia"
[295] "Asia/Novokuznetsk" "Asia/Novosibirsk"
[297] "Asia/Omsk" "Asia/Oral"
[299] "Asia/Phnom_Penh" "Asia/Pontianak"
[301] "Asia/Pyongyang" "Asia/Qatar"
[303] "Asia/Qostanay" "Asia/Qyzylorda"
[305] "Asia/Rangoon" "Asia/Riyadh"
[307] "Asia/Saigon" "Asia/Sakhalin"
[309] "Asia/Samarkand" "Asia/Seoul"
[311] "Asia/Shanghai" "Asia/Singapore"
[313] "Asia/Srednekolymsk" "Asia/Taipei"
[315] "Asia/Tashkent" "Asia/Tbilisi"
[317] "Asia/Tehran" "Asia/Tel_Aviv"
[319] "Asia/Thimbu" "Asia/Thimphu"
[321] "Asia/Tokyo" "Asia/Tomsk"
[323] "Asia/Ujung_Pandang" "Asia/Ulaanbaatar"
[325] "Asia/Ulan_Bator" "Asia/Urumqi"
[327] "Asia/Ust-Nera" "Asia/Vientiane"
[329] "Asia/Vladivostok" "Asia/Yakutsk"
[331] "Asia/Yangon" "Asia/Yekaterinburg"
[333] "Asia/Yerevan" "Atlantic/Azores"
[335] "Atlantic/Bermuda" "Atlantic/Canary"
[337] "Atlantic/Cape_Verde" "Atlantic/Faeroe"
[339] "Atlantic/Faroe" "Atlantic/Jan_Mayen"
[341] "Atlantic/Madeira" "Atlantic/Reykjavik"
[343] "Atlantic/South_Georgia" "Atlantic/St_Helena"
[345] "Atlantic/Stanley" "Australia/ACT"
[347] "Australia/Adelaide" "Australia/Brisbane"
[349] "Australia/Broken_Hill" "Australia/Canberra"
[351] "Australia/Currie" "Australia/Darwin"
[353] "Australia/Eucla" "Australia/Hobart"
[355] "Australia/LHI" "Australia/Lindeman"
[357] "Australia/Lord_Howe" "Australia/Melbourne"
[359] "Australia/North" "Australia/NSW"
[361] "Australia/Perth" "Australia/Queensland"
[363] "Australia/South" "Australia/Sydney"
[365] "Australia/Tasmania" "Australia/Victoria"
[367] "Australia/West" "Australia/Yancowinna"
[369] "Brazil/Acre" "Brazil/DeNoronha"
[371] "Brazil/East" "Brazil/West"
[373] "Canada/Atlantic" "Canada/Central"
[375] "Canada/Eastern" "Canada/Mountain"
[377] "Canada/Newfoundland" "Canada/Pacific"
[379] "Canada/Saskatchewan" "Canada/Yukon"
[381] "CET" "Chile/Continental"
[383] "Chile/EasterIsland" "CST6CDT"
[385] "Cuba" "EET"
[387] "Egypt" "Eire"
[389] "EST" "EST5EDT"
[391] "Etc/GMT" "Etc/GMT-0"
[393] "Etc/GMT-1" "Etc/GMT-10"
[395] "Etc/GMT-11" "Etc/GMT-12"
[397] "Etc/GMT-13" "Etc/GMT-14"
[399] "Etc/GMT-2" "Etc/GMT-3"
[401] "Etc/GMT-4" "Etc/GMT-5"
[403] "Etc/GMT-6" "Etc/GMT-7"
[405] "Etc/GMT-8" "Etc/GMT-9"
[407] "Etc/GMT+0" "Etc/GMT+1"
[409] "Etc/GMT+10" "Etc/GMT+11"
[411] "Etc/GMT+12" "Etc/GMT+2"
[413] "Etc/GMT+3" "Etc/GMT+4"
[415] "Etc/GMT+5" "Etc/GMT+6"
[417] "Etc/GMT+7" "Etc/GMT+8"
[419] "Etc/GMT+9" "Etc/GMT0"
[421] "Etc/Greenwich" "Etc/UCT"
[423] "Etc/Universal" "Etc/UTC"
[425] "Etc/Zulu" "Europe/Amsterdam"
[427] "Europe/Andorra" "Europe/Astrakhan"
[429] "Europe/Athens" "Europe/Belfast"
[431] "Europe/Belgrade" "Europe/Berlin"
[433] "Europe/Bratislava" "Europe/Brussels"
[435] "Europe/Bucharest" "Europe/Budapest"
[437] "Europe/Busingen" "Europe/Chisinau"
[439] "Europe/Copenhagen" "Europe/Dublin"
[441] "Europe/Gibraltar" "Europe/Guernsey"
[443] "Europe/Helsinki" "Europe/Isle_of_Man"
[445] "Europe/Istanbul" "Europe/Jersey"
[447] "Europe/Kaliningrad" "Europe/Kiev"
[449] "Europe/Kirov" "Europe/Lisbon"
[451] "Europe/Ljubljana" "Europe/London"
[453] "Europe/Luxembourg" "Europe/Madrid"
[455] "Europe/Malta" "Europe/Mariehamn"
[457] "Europe/Minsk" "Europe/Monaco"
[459] "Europe/Moscow" "Europe/Nicosia"
[461] "Europe/Oslo" "Europe/Paris"
[463] "Europe/Podgorica" "Europe/Prague"
[465] "Europe/Riga" "Europe/Rome"
[467] "Europe/Samara" "Europe/San_Marino"
[469] "Europe/Sarajevo" "Europe/Saratov"
[471] "Europe/Simferopol" "Europe/Skopje"
[473] "Europe/Sofia" "Europe/Stockholm"
[475] "Europe/Tallinn" "Europe/Tirane"
[477] "Europe/Tiraspol" "Europe/Ulyanovsk"
[479] "Europe/Uzhgorod" "Europe/Vaduz"
[481] "Europe/Vatican" "Europe/Vienna"
[483] "Europe/Vilnius" "Europe/Volgograd"
[485] "Europe/Warsaw" "Europe/Zagreb"
[487] "Europe/Zaporozhye" "Europe/Zurich"
[489] "GB" "GB-Eire"
[491] "GMT" "GMT-0"
[493] "GMT+0" "GMT0"
[495] "Greenwich" "Hongkong"
[497] "HST" "Iceland"
[499] "Indian/Antananarivo" "Indian/Chagos"
[501] "Indian/Christmas" "Indian/Cocos"
[503] "Indian/Comoro" "Indian/Kerguelen"
[505] "Indian/Mahe" "Indian/Maldives"
[507] "Indian/Mauritius" "Indian/Mayotte"
[509] "Indian/Reunion" "Iran"
[511] "Israel" "Jamaica"
[513] "Japan" "Kwajalein"
[515] "Libya" "MET"
[517] "Mexico/BajaNorte" "Mexico/BajaSur"
[519] "Mexico/General" "MST"
[521] "MST7MDT" "Navajo"
[523] "NZ" "NZ-CHAT"
[525] "Pacific/Apia" "Pacific/Auckland"
[527] "Pacific/Bougainville" "Pacific/Chatham"
[529] "Pacific/Chuuk" "Pacific/Easter"
[531] "Pacific/Efate" "Pacific/Enderbury"
[533] "Pacific/Fakaofo" "Pacific/Fiji"
[535] "Pacific/Funafuti" "Pacific/Galapagos"
[537] "Pacific/Gambier" "Pacific/Guadalcanal"
[539] "Pacific/Guam" "Pacific/Honolulu"
[541] "Pacific/Johnston" "Pacific/Kiritimati"
[543] "Pacific/Kosrae" "Pacific/Kwajalein"
[545] "Pacific/Majuro" "Pacific/Marquesas"
[547] "Pacific/Midway" "Pacific/Nauru"
[549] "Pacific/Niue" "Pacific/Norfolk"
[551] "Pacific/Noumea" "Pacific/Pago_Pago"
[553] "Pacific/Palau" "Pacific/Pitcairn"
[555] "Pacific/Pohnpei" "Pacific/Ponape"
[557] "Pacific/Port_Moresby" "Pacific/Rarotonga"
[559] "Pacific/Saipan" "Pacific/Samoa"
[561] "Pacific/Tahiti" "Pacific/Tarawa"
[563] "Pacific/Tongatapu" "Pacific/Truk"
[565] "Pacific/Wake" "Pacific/Wallis"
[567] "Pacific/Yap" "Poland"
[569] "Portugal" "PRC"
[571] "PST8PDT" "ROC"
[573] "ROK" "Singapore"
[575] "Turkey" "UCT"
[577] "Universal" "US/Alaska"
[579] "US/Aleutian" "US/Arizona"
[581] "US/Central" "US/East-Indiana"
[583] "US/Eastern" "US/Hawaii"
[585] "US/Indiana-Starke" "US/Michigan"
[587] "US/Mountain" "US/Pacific"
[589] "US/Pacific-New" "US/Samoa"
[591] "UTC" "W-SU"
[593] "WET" "Zulu"
attr(,"Version")
[1] "2020a"
4. R Dplyr Package
5. R sqldf Package
- 참고 페이지 : https://sqlite.org/lang_datefunc.html
- unixtime : 유닉스시간(unixtime) 타입 시간
| R Programming |
%%R
sqldf(" select datetime(strftime('%s','now'), 'unixepoch') unixtime_datetime,
strftime('%s','now') unixtime_second,
strftime('%Y-%m-%d %H:%M:%S', 'now') now_day,
CURRENT_TIMESTAMP,
datetime('now') now_datetime,
datetime('now','+8 hours') add_datetime,
datetime('now', 'utc') utc_datetime,
time(time(), 'localtime') local_time,
datetime('now', 'localtime') local_datetime_1,
datetime(CURRENT_TIMESTAMP, 'localtime') local_datetime_2,
datetime(strftime('%s','now'), 'unixepoch', 'localtime') local_datetime_3
")
| Results |
unixtime_datetime unixtime_second now_day CURRENT_TIMESTAMP
1 2021-02-12 01:26:02 1613093162 2021-02-12 01:26:02 2021-02-12 01:26:02
now_datetime add_datetime utc_datetime local_time
1 2021-02-12 01:26:02 2021-02-12 09:26:02 2021-02-11 16:26:02 10:26:02
local_datetime_1 local_datetime_2 local_datetime_3
1 2021-02-12 10:26:02 2021-02-12 10:26:02 2021-02-12 10:26:02
6. Python pandasql Package
7. R data.table Package
8. Python Duckdb의 SQL
| Python Programming |
%%sql
select current_timestamp AS today_1,
today() AS today_2,
now() AS current_datetime_1,
transaction_timestamp() AS current_datetime_2,
strftime(current_timestamp,'%S') AS second_1,
epoch(current_timestamp) AS second_2,
strftime(current_timestamp, '%Y-%m-%d %H:%M:%S') AS now_day,
current_timestamp + interval 8 hour AS add_datetime,
strftime(timestamptz '1992-01-01 20:38:40', '%Z') AS TIME_ZONE
| Python Programming |
duckdb.sql(" select current_timestamp AS today_1, \
today() AS today_2, \
now() AS current_datetime_1, \
transaction_timestamp() AS current_datetime_2, \
strftime(current_timestamp,'%S') AS second_1, \
epoch(current_timestamp) AS second_2, \
strftime(current_timestamp, '%Y-%m-%d %H:%M:%S') AS now_day, \
current_timestamp + interval 8 hour AS add_datetime, \
strftime(timestamptz '1992-01-01 20:38:40', '%Z') AS TIME_ZONE ").df()
| Results |
today_1 today_2 \
0 2023-09-03 23:41:57.974000+00:00 2023-09-03
current_datetime_1 current_datetime_2 second_1 \
0 2023-09-03 23:41:57.974000+00:00 2023-09-03 23:41:57.974000+00:00 57
second_2 now_day add_datetime TIME_ZONE
0 1693784517 2023-09-03 23:41:57 2023-09-04 07:41:57.974000+00:00 Etc/UTC
- local time 또는 timezone 변경은 함수에서 직접 지원안하는 듯, ICU extension 방식으로 처리 가능
| Python Programming |
%%sql
load icu;
| Python Programming |
%%sql
SELECT * FROM duckdb_settings() WHERE name = 'TimeZone';
| Results |
name value description input_type
0 TimeZone Etc/UTC The current time zone VARCHAR
| Python Programming |
%%sql
SET TimeZone='Asia/Seoul';
| Python Programming |
%%sql
SELECT * FROM duckdb_settings() WHERE name = 'TimeZone';
| Results |
name value description input_type
0 TimeZone Asia/Seoul The current time zone VARCHAR
| Python Programming |
%%sql
select now()
| Results |
now()
0 2023-08-10 11:04:09.698000+09:00
--------------------------------------------
| [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크 |
| 오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크 |
| [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글