{"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>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>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>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>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>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>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>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>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":29,"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 v27.2 (Yoast SEO v27.2) - 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=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/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":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"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}]}}