點選上方“超哥的雜貨鋪”，輕鬆關注
本文目錄如下：
在前兩篇文章中，我們從多個角度，由淺入深，對比了pandas和SQL在資料處理方面常見的一些操作。
具體來講，第一篇文章
一場pandas與SQL的巔峰大戰
涉及到
資料檢視
，
去重計數
，
條件選擇
，
合併連線
，
分組排序
等操作。
第二篇文章
一場pandas與SQL的巔峰大戰（二）
涉及
字串處理
，
視窗函式
，
行列轉換
，
型別轉換
等操作。您可以點選往期連結進行閱讀回顧。
在日常工作中，我們經常會與日期型別打交道，會在不同的日期格式之間轉來轉去。
本文依然沿著前兩篇文章的思路，對pandas和SQL中的日期操作進行總結，其中SQL採用Hive SQL+MySQL兩種方式，內容與前兩篇相對獨立又彼此互為補充。一起開始學習吧！
◆
◆
◆
◆
◆
資料概況
資料方面，我們依然採用前面文章的訂單資料，樣例如下。在正式開始學習之前，我們需要把資料載入到dataframe和資料表中。本文的資料、程式碼以及清晰的PDF版本可以在公眾號後臺回覆“
對比三
”獲取哦~
pandas載入資料
import
pandas
as
pd
data
= pd。read_excel（
‘order。xlsx’
）
#data2 = pd。read_excel（
‘order。xlsx’
， parse_dates=［
‘ts’
］）
data
。head（）
data
。dtypes
需要指出，pandas讀取資料對於日期型別有特殊的支援。
無論是在read_csv中還是在read_excel中，都有parse_dates引數，可以把資料集中的一列或多列轉成pandas中的日期格式。
上面程式碼中的data是使用預設的引數讀取的，在data。dtypes的結果中ts列是
datetime64［ns］
格式，而data2是顯式指定了ts為日期列，因此data2的ts型別也是
datetime［ns］
。
如果在使用預設方法讀取時，日期列沒有成功轉換，就可以使用類似data2這樣顯式指定的方式。
MySQL載入資料
我準備了一個sql檔案
t_order。sql
，推薦使用navicate客戶端，按照上圖所示方式，直接匯入即可。
Hive載入資料
create
table
`t_order`
（
`id`
int
，
`ts`
string
，
`uid`
string
，
`orderid`
string
，
`amount`
float
）
row
format
delimited
fields
terminated
by
‘，’
stored
as
textfile；
load
data
local
inpath
‘t_order。csv’
overwrite
into
table
t_order；
select
*
from
t_order
limit
20
；
在hive中載入資料我們需要先建立表，然後把文字檔案中的資料load到表中，結果如下圖所示。
我們在MySQL和Hive中都把時間儲存成字串，這在工作中比較常見，使用起來也比較靈活和習慣，因此沒有使用專門的日期型別。
開始學習
我們把日期相關的操作分為
日期獲取
，
日期轉換
，
日期計算
三類。下面開始逐一學習。
日期獲取
1。獲取當前日期，年月日時分秒
pandas中可以使用now（）函式獲取當前時間，但需要再進行一次格式化操作來調整顯示的格式。我們在資料集上新加一列當前時間的操作如下：
MySQL有多個函式可以獲取當前時間：
now（），current_timestamp，current_timestamp（），sysdate（），localtime（），localtime，localtimestamp，localtimestamp（）等。
點選圖片檢視大圖
hive中獲取當前時間，可以使用 current_timestamp（）， current_timestamp，得到的是帶有毫秒的，如果想保持和上面同樣的格式，需要使用substr擷取一下。如下圖所示：
圖中程式碼：
#pandas
data［‘current_dt’］ = pd。datetime。now（）
data［‘current_dt’］ = data［‘current_dt’］。apply（lambda x ： x。strftime（‘%Y-%m-%d %H：%M：%S’））
data。head（）
#也可以data［‘current_dt’］ = pd。datetime。now（）。strftime（‘%Y-%m-%d %H：%M：%S’）一步到位
#MySQL
SELECT
*，
now
（），
current_timestamp
（），
current_timestamp
FROM
`t_order`
；
SELECT
*，
sysdate
（），ocaltime（），
localtime
FROM
`t_order`
；
SELECT
*，
localtimestamp
，
localtimestamp
（）
FROM
`t_order`
；
#HiveQL
select
*，
substr
（
current_timestamp
，
1
，
19
），
substr
（
current_timestamp
（），
1
，
19
）
from
t_order
limit
20
；
2。獲取當前時間，年月日
pandas中似乎沒有直接獲取當前日期的方法，我們沿用上一小節中思路，進行格式轉換得到當前日期。當然這不代表python中的其他模組不能實現，有興趣的朋友可以自己查閱相關文件。
MySQL中可以直接獲取當前日期，使用curdate（）即可，hive中也有相對應的函式：current_date（）。
圖片中的程式碼：
#pandas
data［‘dt_date’］ = pd。datetime。now（）。strftime（‘%Y-%m-%d’）
data。head（）
#MySQL
SELECT
*，
curdate
（）
FROM
`t_order`
；
#HiveQL
select
*，
current_date
（）
from
t_order
limit
20
；
3。提取日期中的相關資訊
日期中包含有年月日時分秒，我們可以用相應的函式進行分別提取。下面我們提取一下ts欄位中的天，時間，年，月，日，時，分，秒資訊。
在MySQL和Hive中，由於ts欄位是字串格式儲存的，我們只需使用字串擷取函式即可。兩者的程式碼是一樣的，只需要注意擷取的位置和長度即可，效果如下：
圖片中程式碼：
#pandas
data［
‘dt_day’
］ = data［
‘ts’
］。dt。date
#提取年月日
data［
‘year’
］ = data［
‘ts’
］。dt。year
#提取年份
data［
‘month’
］ = data［
‘ts’
］。dt。month
#提取月份
data［
‘day’
］ = data［
‘ts’
］。dt。day
#提取天數
data［
‘dt_time’
］ = data［
‘ts’
］。dt。time
#提取時間
data［
‘hour’
］ = data［
‘ts’
］。dt。hour
#提取小時
data［
‘minute’
］ = data［
‘ts’
］。dt。minute
#提取分鐘
data［
‘second’
］ = data［
‘ts’
］。dt。second
#提取秒
data。head（）
#MySQL
select
ts，
substr
（ts，
1
，
10
），
substr
（ts，
1
，
4
），
substr
（ts，
6
，
2
），
substr
（ts，
9
，
2
），
substr
（ts，
12
，
8
），
substr
（ts，
12
，
2
），
substr
（ts，
15
，
2
），
substr
（ts，
18
，
2
）
from t_order；
#HiveQL
select
ts，
substr
（ts，
1
，
10
），
substr
（ts，
1
，
4
），
substr
（ts，
6
，
2
），
substr
（ts，
9
，
2
），
substr
（ts，
12
，
8
），
substr
（ts，
12
，
2
），
substr
（ts，
15
，
2
），
substr
（ts，
18
，
2
）
from t_order limit
20
；
日期轉換
1。可讀日期轉換為unix時間戳
在pandas中，我找到的方法是先將
datetime64［ns］
轉換為字串，再呼叫time模組來實現，程式碼如下：
可以驗證最後一列的十位數字就是ts的時間戳形式。
ps。在此之前，我嘗試了另外一種藉助numpy的方式，進行型別的轉換，但轉出來結果不正確，比期望的結果多8個小時，我寫在這裡，歡迎有經驗的讀者指正。
import
numpy
as
np
data
［
‘ts_timestamp’
］ = （
data
。ts。astype（np。int64）/
1e9
）。astype（np。int64）
data
。head（）
#得到的ts_timestamp結果
#
1564650940
1564653606
1564653875
等剛好比正確的結果多
8
個小時
MySQL和Hive中可以使用時間戳轉換函式進行這項操作，其中MySQL得到的是小數形式，需要進行一下型別轉換，Hive不需要。
圖中程式碼：
#python
def
transfer_time_format
（
x
）：
import time
tmp_time
= time。strptime（x，
‘%Y-%m-%d %H：%M：%S’
）
res_time =
int
（time。mktime（tmp_time））
return
res_time
data［
‘str_ts’
］ = data［
‘ts’
］。dt。strftime（
‘%Y-%m-%d %H：%M：%S’
）
data［
‘str_timestamp’
］ = data［
‘str_ts’
］。apply（transfer_time_format）
data。head（）
#使用匿名函式的寫法
#data［‘str_timestamp’］ = data［‘str_ts’］。apply（lambda x： int（time。mktime（time。strptime（x， ‘%Y-%m-%d %H：%M：%S’））））
#MySQL
select
*， cast（unix_timestamp（ts）
as
int
）
from
t_order；
#Hive
select
*， unix_timestamp（ts）
from
t_order limit
20
；
2。unix時間戳轉換為可讀日期
這一操作為上一小節的逆向操作。
在pandas中，我們看一下如何將str_timestamp列轉換為原來的ts列。這裡依然採用time模組中的方法來實現。
ps。你可能發現了上面程式碼中有一列是ori_dt，雖然看上去是正確的，但格式多少有那麼點奇怪，這也是我在學習過程中看到的一個不那麼正確的寫法，貼出來供大家思考。
data［
‘ori_dt’
］ = pd。to_datetime（data［
‘str_timestamp’
］。values， unit=
‘s’
， utc=
True
）。tz_convert（
‘Asia/Shanghai’
）
data。head（）
#使用預設的pd。to_datetime並不能轉會正確的時間，比實際時間小8個小時
#在網上看到了這種寫法能把8個小時加回來，但顯示的很奇怪。
回到MySQL和Hive，依然只是用一個函式就解決了。
圖中程式碼如下：
#pandas：
def transfer_time_format2（x）：
import time
time_local = time。localtime（x）
res_time = time。strftime（‘%Y-%m-%d %H：%M：%S’， time_local）
return res_time
data［‘ori_ts’］ = data［‘str_timestamp’］。apply（transfer_time_format2）
data。head（）
#MySQL
select
*， from_unixtime（
cast
（
unix_timestamp
（ts）
as
int
））
from
t_order；
#Hive
select
*， from_unixtime（
unix_timestamp
（ts））
from
t_order
limit
20
；
3。10位日期轉8位
對於初始是ts列這樣年月日時分秒的形式，我們通常需要先轉換為10位年月日的格式，再把中間的橫槓替換掉，就可以得到8位的日期了。
由於打算使用字串替換，我們先要將ts轉換為字串的形式，在前面的轉換中，我們生成了一列str_ts，該列的資料型別是object，相當於字串，可以在此基礎上進行這裡的轉換。
MySQL和Hive中也是同樣的套路，擷取和替換幾乎是最簡便的方法了。
圖中程式碼：
#pandas
data［‘str_ts_8’］ = data［‘str_ts’］。astype（str）。str［：10］。apply（lambda x： x。replace（‘-’，‘’））
data。head（）
#MySQL
select
replace
（
substr
（ts，
1
，
10
），
‘-’
，
‘’
）
from
t_order；
#Hive
select
*， regexp_replace（
substr
（ts，
1
，
10
），
‘-’
，
‘’
）
from
t_order
limit
20
；
當然，我們也有另外的解法：使用先將字串轉為unix時間戳的形式，再格式化為8位的日期。
圖中程式碼
：
#MySQL
select
*， from_unixtime（
cast
（
unix_timestamp
（ts）
as
int
），
‘%Y%M%d’
）
from
t_order；
#Hive
select
*， from_unixtime（
unix_timestamp
（ts），
‘yyyyMMdd’
）
from
t_order
limit
20
；
pandas中我們也可以直接在unix時間戳的基礎上進行操作，轉為8位的日期。具體做法只要上面的transfer_time_format2函式即可，效果如下圖所示。
def
transfer_time_format3
（x）
：
import
time
time_local = time。localtime（x）
res_time = time。strftime（
‘%Y%m%d’
， time_local）
#改這裡的格式就好
return
res_time
data［
‘str_ts_8_2’
］ = data［
‘str_timestamp’
］。apply（transfer_time_format3）
data。head（）
4。8位日期轉10位
這一操作同樣為上一小節的逆向操作。
結合上一小節，實現10位轉8位，我們至少有兩種思路。可以進行先擷取後拼接，把橫線
-
拼接在日期之間即可。二是藉助於unix時間戳進行中轉。SQL中兩種方法都很容易實現，在pandas我們還有另外的方式。
方法一：
pandas中的拼接也是需要轉化為字串進行。如下：
MySQL和Hive中，可以使用concat函式進行拼接：
圖中程式碼如下：
#python
data［‘str_ts_10’］ = data［‘str_ts_8’］。apply（lambda x ： x［：4］ + “-” + x［4：6］ + “-” + x［6：］）
data。head（）
#MySQL
select
id
， ts，
concat
（
substr
（dt8，
1
，
4
），
‘-’
，
substr
（dt8，
5
，
2
），
‘-’
，
substr
（dt8，
7
，
2
））
from
（
select
*，
replace
（
substr
（ts，
1
，
10
），
‘-’
，
‘’
）
as
dt8
from
t_order
） a
#Hive
select
id
， ts，
concat
（
substr
（dt8，
1
，
4
），
‘-’
，
substr
（dt8，
5
，
2
），
‘-’
，
substr
（dt8，
7
，
2
））
from
（
select
*， regexp_replace（
substr
（ts，
1
，
10
），
‘-’
，
‘’
）
as
dt8
from
t_order
） a
limit
20
；
方法二，透過unix時間戳轉換：
在pandas中，藉助unix時間戳轉換並不方便，我們可以使用datetime模組的格式化函式來實現，如下所示。
Mysql和Hive中unix_timestamp接收的引數不一樣，前者必須輸入為整數，後者可以為字串。我們的目標是輸入一個8位的時間字串，輸出一個10位的時間字串。由於原始資料集中沒有8位時間，我們臨時構造了一個。程式碼如下：
圖中程式碼如下：
#pandas
def transfer_time_format4（x）：
from datetime import datetime
tmp_time = datetime。strptime（‘20190801’， ‘%Y%m%d’）
res_time = datetime。strftime（tmp_time， ‘%Y-%m-%d’）
return res_time
data［‘str_ts_10_2’］ = data［‘str_ts_8’］。apply（transfer_time_format4）
data。head（）
#MySQL
select
*，
replace
（
substr
（ts，
1
，
10
），
‘-’
，
‘’
），
from_unixtime（
unix_timestamp
（
cast
（
replace
（
substr
（ts，
1
，
10
），
‘-’
，
‘’
）
as
int
）），
‘%Y-%m-%d’
）
from
t_order
；
#Hive
select
*，
regexp_replace（
substr
（ts，
1
，
10
），
‘-’
，
‘’
），
from_unixtime（
unix_timestamp
（regexp_replace（
substr
（ts，
1
，
10
），
‘-’
，
‘’
），
‘yyyyMMdd’
），
‘yyyy-MM-dd’
）
from
t_order
limit
20
；
ps。關於時間Hive中的時間轉換，我在之前總結Hive函式的文章的最後一部分中已經有過梳理，例子比此處更加具體，歡迎翻閱
：
常用Hive函式的學習和總結
日期計算
日期計算主要包括日期間隔（加減一個數變為另一個日期）和計算兩個日期之間的差值。
1。日期間隔
pandas中對於日期間隔的計算需要藉助datetime 模組。我們來看一下如何計算ts之後5天和之前3天。
使用timedelta函式既可以實現天為單位的日期間隔，也可以按周，分鐘，秒等進行計算。
在MySQL和Hive中有相應的日期間隔函式date_add，date_sub函式，但使用的格式略有差異。
需要注意的是Hive計算的結果沒有時分秒，如果需要，依然可以使用拼接的方式獲得，此處略。
2。日期差
這一小節仍然是上一小節的逆操作。（怎麼這麼多逆操作，累不累啊……）我們來看一下如何計算兩個時間的日期差。
在pandas中，如果事件型別是datetime64［ns］型別，直接作差就可以得出日期差，但是得到的資料後面還有一個“days”的單位，這其實就是上一小節提到的timedelta型別。
為了便於使用，我們使用map函式獲取其days屬性，得到我們想要的數值的差。如下所示：
如果不是datetime格式，可以先用下面的程式碼進行一次轉換。
#str_ts是字串格式，轉換出的dt_ts是datetime64［ns］格式
data［
‘dt_ts’
］ = pd。to_datetime（data［
‘str_ts’
］，
format
=
‘%Y-%m-%d %H：%M：%S’
）
Hive和MySQL中的日期差有相應的函式datediff。但需要注意它的輸入格式。
可以看到輸入的形式既可以是具體到時分秒的格式，也可以是年月日格式。但是要注意Hive中輸入的日期必須是10位的格式，否則得不到正確的結果，比如輸入8位的，結果會是NULL，而MySQL則可以進行8位日期的計算。
◆
◆
◆
◆
◆
小結
本文涉及到的對比操作和相應的解法如上圖所示。整體看起來比之前的要“亂”一些，但仔細看看並沒有多少內容。
需要指出，關於日期操作，本文只是總結了一些pandas和SQL都有的部分操作，也都是比較常見的。python中和SQL本身關於日期操作還有很多其他用法，限於時間關係就省略了。
由於時間匆忙，行文不當之處還請多多包含。如果你有好的想法，歡迎一起交流學習。本文的程式碼和資料可以在公眾號後臺回覆“
對比三
”獲取，祝學習愉快！
以清淨心看世界；
用歡喜心過生活。
超哥的雜貨鋪，你值得擁有~
新增微信hitchenghengchao進入交流群~
長按二維碼關注我們
推薦閱讀：
1。
一場pandas與SQL的巔峰大戰
2。
一場pandas與SQL的巔峰大戰（二）
3。
常用Hive函式的學習和總結
