The tag has no usage guidance.

learn more… | top users | synonyms

42
votes
2answers
64k 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 ...
16
votes
2answers
42k 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(); +---------------------+ | ...
14
votes
1answer
1k 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 ...
12
votes
2answers
46k 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 ...
7
votes
3answers
3k 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 ...
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’, ...
7
votes
1answer
7k 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 ...
6
votes
2answers
2k 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 ...
6
votes
2answers
125 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
2answers
8k 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 ...
6
votes
1answer
592 views

MySQL year 2038 vulnerable

I was studying Drupal and asked this question: http://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
2answers
2k 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 ...
5
votes
5answers
2k 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 ...
4
votes
4answers
34k 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. ...
4
votes
1answer
175 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 ...
4
votes
2answers
11k 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
2answers
88 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
1k 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 ...
4
votes
1answer
144 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
2answers
239 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 ...
3
votes
1answer
10k 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" ( ...
3
votes
1answer
4k 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 ...
3
votes
2answers
133 views

SQL server 2000 transactions with the same time

I have a BIG problem here. I'm creating a program that can be looping if the connection to the server is broken. But when the program is running for a few months, I found a bug on SQL server 2000. I ...
3
votes
1answer
375 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 ...
3
votes
2answers
959 views

sql server management studio 2008 sucks up with memory yielding .net runtime failure

PROBLEM sql server management studio 2008 sucks up with memory yielding a .net runtime error. It crashes with an empty message in an error-dialog but there can be found a little eventlog entry, ...
3
votes
2answers
70 views

Generate a sequence of timestamps but skip given periods

I have a table things_to_do in PostgreSQL. For example: thing_id --------- 1 2 3 4 5 6 And I want to add a timestamp to every row: SELECT thing_id, '2016-02-10 19:57:00'::timestamp ...
3
votes
1answer
574 views

current_timestamp default column value working in Windows but not in Linux

This MySql query runs fine in Windows: CREATE TABLE `posts`.`table` ( `idTable` INT NOT NULL AUTO_INCREMENT, `Column1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`idTable`)); In ...
3
votes
1answer
4k views

Why is add timestamp column with default not null so slow in Oracle 11g?

In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production. This ...
3
votes
1answer
846 views

Timestamp on SSIS update on tsql

I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be ...
3
votes
1answer
983 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 ...
3
votes
3answers
4k 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 ...
2
votes
2answers
25k 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 ...
2
votes
3answers
840 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
2
votes
2answers
25k 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 ...
2
votes
1answer
12k 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 ...
2
votes
3answers
4k views

Sorting by DATETIME slower than by TIMESTAMP?

I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ? Because I noticed that ordering by numbered keys is done ...
2
votes
2answers
11k 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 ...
2
votes
1answer
12k 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 ...
2
votes
2answers
3k views

How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?

I want to set `addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed: mysql> alter table mytable change c1 ...
2
votes
1answer
463 views

How to generate time in milliseconds in PostgreSQL stored procedure?

I have been reading the Postgres documentation about timestamps, but I am still a little bit confused. I need to store moments in times, measured in milliseconds, between the current time and ...
2
votes
1answer
546 views

datetime vs timestamp in mysql 5.5

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this? I have a situation where I am trying ...
2
votes
1answer
451 views

MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
2
votes
2answers
146 views

Automatically set timestamps for “create” and “modified”?

I have currently following SQL table: CREATE TABLE IF NOT EXISTS `xyz` ( `id` bigint(20) NOT NULL, `guid` char(36) NOT NULL, `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
2
votes
1answer
4k views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
2
votes
1answer
351 views

Implications of a second index on a timestamp field in a sequential table

I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp ...
2
votes
1answer
4k 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, ...
2
votes
3answers
4k views

How do i subtract a number of days from a prepared statement parameter in db2?

I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( ...
2
votes
1answer
11k views

How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time). As I understand it, the DATETIME is set to ...
2
votes
0answers
821 views

Logic to convert a UTC date-time to a specific time zone

I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred. I have a DIM ...
1
vote
2answers
499 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. ...