{"id":165252,"date":"2020-10-02T06:35:42","date_gmt":"2020-10-01T22:35:42","guid":{"rendered":"http:\/\/4563.org\/?p=165252"},"modified":"2020-10-02T06:35:42","modified_gmt":"2020-10-01T22:35:42","slug":"postgres-to_timestamp-%e6%97%a0%e6%b3%95%e8%bd%ac%e6%8d%a2-unix-epoch-%e5%ad%97%e7%ac%a6%e4%b8%b2","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=165252","title":{"rendered":"Postgres to_timestamp() \u65e0\u6cd5\u8f6c\u6362 unix epoch \u5b57\u7b26\u4e32"},"content":{"rendered":"
\n
\n
\n

Postgres to_timestamp() \u65e0\u6cd5\u8f6c\u6362 unix epoch \u5b57\u7b26\u4e32 <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span> <\/i> 2<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
<\/p>\n

\u8868\u7ed3\u6784\u5982\u4e0b\uff1a<\/p>\n

create table item (  \"ID\" varchar not null,  \"unixTime\" varchar not null, ); <\/code><\/pre>\n
\u73b0\u5728<\/p>\n
SELECT TO_TIMESTAMP(\"unixTime\"); <\/code><\/pre>\n
\u5c31\u603b\u662f\u63d0\u793a
 ERROR: function to_timestamp(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.<\/code><\/p>\n
\u4f46\u968f\u624b\u8bd5\u4e86\u51e0\u4e2a\u503c\u90fd\u6ca1\u5565\u95ee\u9898\uff0c\u6bd4\u5982\uff1a
 select to_timestamp(‘1358121600’);
 select to_timestamp(‘969408000’);<\/p>\n
\u521d\u6b65\u6000\u7591\u8868\u91cc\u6709\u4e0d\u540c format \u7684 timestamp \u7684\u503c\uff0c\u7136\u540e to_timestamp()\u4e0d\u80fd\u6839\u636e\u4e0d\u540c\u7684 format \u81ea\u52a8\u667a\u80fd\u8f6c\u6362\uff1f
 \u6709\u6ca1\u6709\u5927\u4f6c\u53ef\u4ee5\u542f\u53d1\u4e0b\uff0c\u770b\u8fd9\u6837\u7684\u60c5\u51b5\u600e\u4e48\u89e3\u51b3\uff0c\u6025\u6025\u6025\uff5e\u8c22\u8c22\u8c22\u8c22<\/p>\n<\/p><\/div>\n
 \u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (12<\/span>)        <\/div>\n
 <\/div>\n<\/p><\/div>\n<\/p><\/div>\n
    \n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : hooopo <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 SELECT TO_TIMESTAMP(unixTime);                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @hooopo \u8fd9\u4e2a\u4e0d\u662f\u95ee\u9898\u5566\uff0cpostgres \u91cc\u5b57\u6bb5\u540d\u6709\u5927\u5199\u7684\u8bdd\u8981\u52a0\u53cc\u5f15\u53f7\u7684\uff5e                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : hooopo <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u5f88\u7b80\u5355\u554a \u5148\u7528 limit 1 \u6d4b\u8bd5\u4e0b\u662f\u4e0d\u662f\u6570\u636e\u95ee\u9898 \u6570\u636e\u95ee\u9898\u5c31\u4fee\u6570\u636e                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @hooopo Limit 1 \u4e5f\u62a5\u9519\uff0c\u800c\u4e14 80M \u6761\u6570\u636e\uff0c\u6ca1\u6cd5\u6d4b\u5440\uff0c\u6211\u5012\u662f\u60f3\u6709\u4e2a\u51fd\u6570\u53ef\u4ee5\u628a\u8fd9\u4e9b invalid \u7684\u6570\u636e\u6392\u9664\u51fa\u53bb\u3002\u3002\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : hooopo <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u4e00\u4e2a\u6b63\u5219\u5c31\u884c\u554a                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : MoYi123 <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 select TO_TIMESTAMP(“unixTime”::int) from item;                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @MoYi123 \u5b8c\u7f8e\uff01\uff01\u611f\u8c22\u611f\u8c22\u8001\u54e5                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : sagaxu <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u4e0d\u80fd\u67e5\u4e00\u4e0b\u624b\u518c\u770b\u770b\u8fd9\u4e2a\u51fd\u6570\u7684\u5b9a\u4e49\uff1f                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : sfqtsh <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 df to_timestamp<\/p>\n
    \u4f60\u770b\u6709 character varying \u53c2\u6570\u7684\u5417\uff1f<\/p>\n
    \u663e\u7136\u6ca1\u6709\uff01                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @sfqtsh \u6709\u652f\u6301 string \u554a                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : sfqtsh <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @nuistzhou \u90a3\u662f format function \u4e00\u79cd\uff0c\u4e14\u6709\u4e24\u4e2a\u53c2\u6570\u3002<\/p>\n
    to_timestamp ( text, text ) \u2192 timestamp with time zone<\/p>\n
    Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.32.)<\/p>\n
    to_timestamp(’05 Dec 2000′, ‘DD Mon YYYY’) \u2192 2000-12-05 00:00:00-05                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @sfqtsh \u54e6\uff0c\u4f60\u8bf4\u7684\u5bf9\u3002
    \u6211\u9700\u8981\u7684\u5176\u5b9e\u662f\u8fd9\u4e2a\uff1a
    “`
    to_timestamp ( double precision ) \u2192 timestamp with time zone<\/p>\n
    Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone<\/p>\n
    to_timestamp(1284352323) \u2192 2010-09-13 04:32:03+00
    “`                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n","protected":false},"excerpt":{"rendered":"
    Postgres to_times…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/165252"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=165252"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/165252\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=165252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=165252"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=165252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}