Questions tagged [timestamp]

For questions about timestamp data types and/or recording a particular point in time.

Filter by
Sorted by
Tagged with
89
votes
2answers
151k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
44
votes
2answers
41k views

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ------------...
33
votes
4answers
105k views

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | ...
28
votes
2answers
99k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM mytable;...
19
votes
2answers
31k views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 ...
18
votes
2answers
103k views

Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47. Query is ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ; While it is working ...
16
votes
1answer
2k views

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH ...
15
votes
1answer
42k views

How to convert a unix time to PostgreSQL' Timstamp without timezome?

I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30) I have some data in a table which is created like this: CREATE TABLE "CLOUDDATA" ( ...
12
votes
3answers
5k views

“AT TIME ZONE” with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ---------------+--...
10
votes
3answers
4k views

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY? Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?
10
votes
3answers
18k views

Storing time with milliseconds in database

Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds). I was originally going to ...
8
votes
3answers
4k views

Best way to handle dates prior to 1000 A.D. in MySQL?

I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000. Is there a way that would be more convenient than either ...
7
votes
4answers
75k views

Convert a Unix timestamp to a DATETIME in a View

I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out. ...
7
votes
2answers
2k views

Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values? ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’...
6
votes
1answer
19k views

Why does mysql adds a default timestamp on insert/update even if I did not specify anything?

I have a table that is as follows: CREATE TABLE SomeTable ( id int unsigned NOT NULL AUTO_INCREMENT, status enum('broken','repaired') NOT NULL, from datetime NOT NULL, until ...
6
votes
2answers
8k views

How to change only time while keeping the date same in timestamp type

I have timestamp column used for scheduling calendar events. I would like to change only the time part while retaining the date part untouched. How can I possibly do it? I'm on postgresql Current: ...
6
votes
2answers
716 views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
6
votes
1answer
1k views

Oddities with AT TIME ZONE and UTC offsets

I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result: select timezone('America/Chicago', '2017-01-01 12:00:00'::...
6
votes
2answers
32k views

MySQL timestamp timezone handling

Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be ...
6
votes
1answer
36k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
6
votes
1answer
887 views

MySQL year 2038 vulnerable

I was studying Drupal and asked this question: https://drupal.stackexchange.com/questions/45443/why-timestamp-format-was-chosen-for-users-created-field/45446#45446 I decided to check, how the CMS ...
5
votes
4answers
92k views

How do I get the difference in minutes from 2 timestamp columns?

Can't seem to figure this out. Just need the difference in minutes between 2 oracle timestamp columns. END_TS START_TS ------------------------------------------------...
5
votes
5answers
5k views

Using timestamp plus integer as primary key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
5
votes
2answers
6k views

Default value for “microtime” column in MySQL

My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function. ...
5
votes
1answer
12k views

How to get the difference in days between 2 timestamps in PostgreSQL

I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not ...
5
votes
1answer
8k views

What happens when changing column type DATETIME to TIMESTAMP in MySQL?

I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines. Is it possible? Is it slow? Will I lose the old information or MySQL can convert this ...
5
votes
2answers
2k views

Schema for Financial Database

My company is starting a new initiative aimed at building a financial database from scratch. We would be using it in these ways: Time series analysis of: a company's financial data (ex: IBM's total ...
5
votes
2answers
2k views

Alternative to sequence and timestamp: uniquely ordering records in time

Oracle 11gR2 Exadata I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted ...
5
votes
1answer
2k views

Postgresql partitioned table timestamptz constraint problem

The table reports is partitioned table by day like reports_20170414,reports_20170415 Constraint SQL is defined as follow CHECK ( rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time ...
5
votes
1answer
6k views

MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table: CREATE TABLE `judgements` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `...
4
votes
2answers
521 views

What function to use with this timestamp?

I have timestamp (stored as BIGINT) format I cannot turn into a human-readable format: 1462975819250 It points to a date in 2016-may-11th. However function to_timestamp() turns this into: 48329-11-...
4
votes
1answer
187 views

How can I improve this DB query? it takes too much time

The query is this: $today = $date->getTimestamp(); SELECT t.*, u.screen_name, u.profile_image_url FROM table AS t LEFT JOIN users AS u ON t.user_id=u.id_str WHERE UNIX_TIMESTAMP(t....
4
votes
2answers
182 views

Different syntax to add/substract interval

Till now I used the following syntax to add an interval to a timestamp: select now() + '5 year'; This worked fine till I tried to subtract the interval which results in a syntax error. invalid ...
4
votes
2answers
23k views

How can I time-stamp data in MongoDB?

I'm responsible for writing an app to juggle a huge amount of client and business intelligence information. This information involves records for various businesses and metrics like revenue, their web ...
4
votes
1answer
1k views

“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp

I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table): select to_timestamp(...
4
votes
2answers
100 views

List minutes instead of grouping by them

Looking how to list minutes 1 to 120 from a 2 hour interval timestamp field instead of getting each minute from both hours together. What I want is something like this: min COUNT(*) 1 ...
4
votes
2answers
75 views

Get total amount of time between records when changed state

I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown. So the data is stored like this: device_owner ...
4
votes
3answers
9k views

Finding five-minute interval prior to current time

I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as expected....
4
votes
1answer
495 views

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format: SET TIME ZONE '+02:00'; At least I assume, because checking the ...
4
votes
2answers
412 views

Can a single SQL Server instance issue the same SYSDATETIME twice?

I am contemplating using a datetime2(7) as a clustered index. If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming ...
4
votes
1answer
7k views

Exchange rate table and conversion on the fly in query

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items. I'm working with Java and my first thought was that I would use some kind ...
4
votes
1answer
2k views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
4
votes
1answer
642 views

Is there a strategy to use timestamp as primary key?

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that ...
3
votes
1answer
11k views

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function ...
3
votes
3answers
227 views

Calculations using two tables with different date frequencies

I'm looking to align dates in one table with dates in another table that have different frequencies. I want the values associated with the low frequency table to repeat until there is a new date so I ...
3
votes
1answer
755 views

Is there way to get transaction commit timestamp in Postgres?

I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way: SELECT * FROM my_table WHERE ...
3
votes
1answer
7k views

dual table postgres doesn't return timestamp

I am trying to select now() from my local postgres database dual table: select now() from dual; It returns no rows. Just prints column name now(yyyy-MM-dd HH:mm:ss.ffffff) What is it that I am ...
3
votes
2answers
45k views

Comparing date and timestamp variables in a where clause in a procedure

I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
3
votes
1answer
3k views

Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone. F.e. the date 2018-08-...
3
votes
3answers
924 views

Is timestamptz preferred when timezone logic is performed by the client?

I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with. The general advice seems to be always use timestamptz,...