Skip to content

Instantly share code, notes, and snippets.

@CooperLuan

CooperLuan/20151128_udf_oid_2_gentime.sql

Created November 28, 2015 05:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save CooperLuan/140249b760176412183e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save CooperLuan/140249b760176412183e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
20151128_udf_oid_2_gentime.sql
/*
* 将 Mongo 生成的 ObjectId 转为 datetime/timestamp
* 考虑时区因素
*/
DROP FUNCTION f_oid_2_gen_time(oid varchar);
CREATE FUNCTION f_oid_2_gen_time
(oid varchar(30))
RETURNS timestamp
IMMUTABLE
AS $$
import struct
import datetime
from dateutil.relativedelta import relativedelta
__id = oid.decode('hex')
t = struct.unpack(">i", __id[0:4])[0]
return datetime.datetime.fromtimestamp(t) + relativedelta(hours=8)
$$ LANGUAGE plpythonu;
SELECT f_oid_2_gen_time('565818ffea3faf69f09727e6');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment