{"id":21562,"date":"2023-01-13T11:33:19","date_gmt":"2023-01-13T10:33:19","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=21562"},"modified":"2023-01-13T11:33:20","modified_gmt":"2023-01-13T10:33:20","slug":"converting-from-unix-timestamps-to-real-timestamps-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/","title":{"rendered":"Converting from Unix timestamps to &#8220;real&#8221; timestamps in PostgreSQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Recently we&#8217;ve been asked how an application can be changed from using <a href=\"https:\/\/en.wikipedia.org\/wiki\/Unix_time\" target=\"_blank\" rel=\"noreferrer noopener\">Unix timestamps<\/a> to PostgreSQL <a href=\"https:\/\/www.postgresql.org\/docs\/current\/datatype-datetime.html\" target=\"_blank\" rel=\"noreferrer noopener\">timestamps<\/a>. It is still not so uncommon to store dates and times in numeric data types, but if you want to work with timestamps you should use the timestamp (with timezone) data type, and not abuse other data types for that.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lets do a little example on how that looked like. To get the current Unix timestamp in PostgreSQL you simply have to do this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select extract(epoch from now());\n      extract      \n-------------------\n 1673605104.930035\n(1 row)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">If you are only interested in the seconds, use the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/functions-math.html\" target=\"_blank\" rel=\"noreferrer noopener\">trunc<\/a> function around it:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select trunc(extract(epoch from now()));\n   trunc    \n------------\n 1673605167\n(1 row)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Here is a very simple table which contains a primary key and a Unix timestamp wrapped into a bigint:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3,12]; title: ; notranslate\" title=\"\">\npostgres=# create table t ( a int primary key generated always as identity, b bigint );\nCREATE TABLE\npostgres=# \\d t\n                            Table &quot;public.t&quot;\n Column |  Type   | Collation | Nullable |           Default            \n--------+---------+-----------+----------+------------------------------\n a      | integer |           | not null | generated always as identity\n b      | bigint  |           |          | \nIndexes:\n    &quot;t_pkey&quot; PRIMARY KEY, btree (a)\n\npostgres=# insert into t(b) select trunc(extract(epoch from now())) from generate_series(1,1000) i;\nINSERT 0 1000\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Lets assume this is the table of the application which needs to be converted to use real timestamps. The data currently looks like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select * from t limit 10;\n a  |     b      \n----+------------\n  1 | 1673603675\n  2 | 1673603675\n  3 | 1673603675\n  4 | 1673603675\n  5 | 1673603675\n  6 | 1673603675\n  7 | 1673603675\n  8 | 1673603675\n  9 | 1673603675\n 10 | 1673603675\n(10 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">To convert that to a real timestamp you can make use of generated columns:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# alter table t add column c timestamptz generated always as (to_timestamp(b)) stored;\nALTER TABLE\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Once that is in place, you can ask for the timestamp of all the rows in the table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select * from t limit 5;\n a |     b      |           c            \n---+------------+------------------------\n 1 | 1673603675 | 2023-01-13 10:54:35+01\n 2 | 1673603675 | 2023-01-13 10:54:35+01\n 3 | 1673603675 | 2023-01-13 10:54:35+01\n 4 | 1673603675 | 2023-01-13 10:54:35+01\n 5 | 1673603675 | 2023-01-13 10:54:35+01\n(5 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Now you can either get rid of the bigint column immediately or take your time to adjust the application and get rid of it some time later.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently we&#8217;ve been asked how an application can be changed from using Unix timestamps to PostgreSQL timestamps. It is still not so uncommon to store dates and times in numeric data types, but if you want to work with timestamps you should use the timestamp (with timezone) data type, and not abuse other data types [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[2602],"type_dbi":[],"class_list":["post-21562","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql-2"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Converting from Unix timestamps to &quot;real&quot; timestamps in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Converting from Unix timestamps to &quot;real&quot; timestamps in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"Recently we&#8217;ve been asked how an application can be changed from using Unix timestamps to PostgreSQL timestamps. It is still not so uncommon to store dates and times in numeric data types, but if you want to work with timestamps you should use the timestamp (with timezone) data type, and not abuse other data types [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-13T10:33:19+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-01-13T10:33:20+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Converting from Unix timestamps to &#8220;real&#8221; timestamps in PostgreSQL\",\"datePublished\":\"2023-01-13T10:33:19+00:00\",\"dateModified\":\"2023-01-13T10:33:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/\"},\"wordCount\":207,\"commentCount\":0,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/\",\"name\":\"Converting from Unix timestamps to \\\"real\\\" timestamps in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-01-13T10:33:19+00:00\",\"dateModified\":\"2023-01-13T10:33:20+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Converting from Unix timestamps to &#8220;real&#8221; timestamps in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Converting from Unix timestamps to \"real\" timestamps in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Converting from Unix timestamps to \"real\" timestamps in PostgreSQL","og_description":"Recently we&#8217;ve been asked how an application can be changed from using Unix timestamps to PostgreSQL timestamps. It is still not so uncommon to store dates and times in numeric data types, but if you want to work with timestamps you should use the timestamp (with timezone) data type, and not abuse other data types [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2023-01-13T10:33:19+00:00","article_modified_time":"2023-01-13T10:33:20+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Converting from Unix timestamps to &#8220;real&#8221; timestamps in PostgreSQL","datePublished":"2023-01-13T10:33:19+00:00","dateModified":"2023-01-13T10:33:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/"},"wordCount":207,"commentCount":0,"keywords":["postgresql"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/","name":"Converting from Unix timestamps to \"real\" timestamps in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-01-13T10:33:19+00:00","dateModified":"2023-01-13T10:33:20+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/converting-from-unix-timestamps-to-real-timestamps-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Converting from Unix timestamps to &#8220;real&#8221; timestamps in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21562","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=21562"}],"version-history":[{"count":4,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21562\/revisions"}],"predecessor-version":[{"id":21583,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21562\/revisions\/21583"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=21562"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=21562"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=21562"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=21562"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}