The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
2answers
38 views

How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone? I tried the following: CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ( ...
3
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 ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
3
votes
2answers
315 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: ...
0
votes
1answer
84 views

localtimestamp or curent_timestamp for default value

In Oracle-12c I have some columns that I am converting from timestamp with time zone to timestamp with local time zone. Some of those same columns also currently have a default value of ...
0
votes
2answers
25 views

Execute query using daily timestamp (mysql event)

I was trying to find a way for give users a daily "point" per online day. So my plan was setup a MySQL event to run this query: UPDATE users SET points=points+1 WHERE timestamp > "X" (Where X ...
1
vote
1answer
97 views

DB2 LUW Timestamp differences in versions

Im new to DB2, my back ground is in MS SQL server 2000 thru 2012. I have a developer using Local DB DB2 LUW v9.7.500.4299 FIXPACK_NUM = 5 She is selecting some data from a table where the field ...
1
vote
1answer
195 views

PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well ...
6
votes
2answers
129 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 ...
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 ...
0
votes
0answers
29 views

How does the current_timestamp() or now() work? I am getting inconsistent results

current_timestamp() returns time ahead of current time and then returns current time upon subsequent request. Below are two requests i gave to the mysql server in commandline. I was not able to ...
1
vote
1answer
73 views

Converting UTC time to timestamptz always adds the +01 offset in Postgres

In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone: SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC' this outputs correctly ...
3
votes
2answers
76 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 ...
1
vote
1answer
67 views

Filter created_at and count distinct minutes

I have this query to get records from a database: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp 'yesterday' AND created_at < timestamp 'today' ...
12
votes
2answers
47k 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 ...
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 ...
1
vote
1answer
43 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do ...
1
vote
1answer
77 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
4
votes
1answer
178 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 ...
3
votes
1answer
657 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 ...
16
votes
2answers
43k 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(); +---------------------+ | ...
0
votes
1answer
40 views

Select Unique Fields with Max Values in Each?

I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is. Addy (text), NW (number), UDate ...
2
votes
2answers
165 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
0answers
832 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 ...
0
votes
1answer
64 views

Filter column by records matching a timezone in Postgres

I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not ...
4
votes
4answers
35k 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. ...
1
vote
2answers
609 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. ...
0
votes
0answers
62 views

Syntax to change a DB2 timestamp from CST to EST

I am writing a code and the source are two DB2 databases. I see that both of them have a different timestamps. (one shows CST and another is EST). I need to make sure that both of them have similar ...
1
vote
1answer
141 views

Table replication with extra TIMESTAMP column on slave

I have a table on master and same table on slave with an extra column like InsertTime timestamp DEFAULT CURRENT_TIMESTAMP I want to keep record of time when there is update on slave. Replication ...
8
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 ...
0
votes
1answer
3k views

How to convert timestamp / rowversion to a date and time in SQL Server?

How to convert timestamp / rowversion to a date and time in SQL Server? I have several tables where I would like to track the last update, with column names like ModifiedAt, UpdatedAt, UpdatedDate. ...
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 ...
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 ...
3
votes
1answer
465 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
134 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 ...
0
votes
1answer
881 views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
0
votes
1answer
42 views

Unix timestamp conversion

How can I output my Unix timestamps to human readable GMT? SELECT category ,fullname ,shortname ,idnumber ,startdate ,visible ,timecreated ...
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’, ...
0
votes
1answer
1k views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
1
vote
2answers
122 views

Mysql date_add returns wrong row in python, right row in phpmyadmin [closed]

I am trying to get the first and last record from a month. In phpmyadmin I ge the correct result. In python I don't. In fact python returns the same row for both of these queries. First number is ...
0
votes
0answers
63 views

compare time difference between current time with last data timestamp

I have a data server recording data every second. i need to setup a cron job that that runs a script that can give a reply if the now() is 24 hours more than the last data recorded. So that I can ...
1
vote
2answers
57 views

Organize repeating timestamped values in schema

I am creating models to hold some financial data. The information frequently is split into timestamped values, e.g: Some value, now | as of Jan 1st | as of Jun 1st | as of Sep 1st There are ...
1
vote
3answers
47 views

Last occurance of time relative to now()

What's the most elegant way to ask Postgres for the last 4:05pm relative to now()? I have been writing a fairly elaborate CASE with date_trunc's and my guess is it can be accomplished in a much ...
0
votes
3answers
334 views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
-4
votes
1answer
30 views

Get currentmilliseconds in postgres

I'm working on timestamp/datetime in my application. I'm using postgres db. What is the way to get current milliseconds (something like 1412706599000) in postgres?
1
vote
1answer
1k views

Time series and gap filling [closed]

I have to choose a database server for storing big time series data. Currently I use PostgreSQL, but I've found that Vertica supports automatic gap filling for missing data and that's really nice. ...
1
vote
2answers
186 views

fixed length table

I only have ever used mysql and storing data on .txt files. Currently I'm working with tick data (I have node.js on my server). My custom setup so far stores a fixed amount of tick data as comma ...
2
votes
2answers
26k 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 ...
0
votes
1answer
139 views

Query MST instead of UTC for timestamps

on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates) ...
0
votes
2answers
331 views

Keep trailing zeros on Timestamp SELECT

This is the issue: I am reading rows with a timestamp column from a table and I need to make sure that the database returns the timestamp with a fixed width. But I am encountering instances where the ...